310 lines
9.6 KiB
SQL
310 lines
9.6 KiB
SQL
USE Arizona;
|
|
|
|
BEGIN TRANSACTION;
|
|
SET XACT_ABORT ON;
|
|
|
|
|
|
|
|
/* OCTPDBA-414: table dbo.criteria, pharmacy only*/
|
|
|
|
/* CR_code, CR_criteria_type */
|
|
INSERT INTO [Arizona].[upd].[IA_IndexAlignementActions]
|
|
(
|
|
[IA_executionDate],
|
|
[IA_schemaName],
|
|
[IA_tableName],
|
|
[IA_columnsName],
|
|
[IA_isClustered],
|
|
[IA_isUnique],
|
|
[IA_includedColumns],
|
|
[IA_isToBeDeleted],
|
|
[IA_isToBeRecreated],
|
|
[IA_expectedIndexName],
|
|
[IA_foundIndexName],
|
|
[IA_wasIndexFound],
|
|
[IA_wasCreated],
|
|
[IA_wasRenamed],
|
|
[IA_wasDeleted],
|
|
[IA_wasRecreated],
|
|
[IA_includedColumnsMatch],
|
|
[IA_actualColumns],
|
|
[IA_actualIncludedColumns],
|
|
[IA_statusMsg],
|
|
[IA_indexOptions],
|
|
[IA_reference]
|
|
)
|
|
SELECT
|
|
NULL as [IA_executionDate],
|
|
'dbo' as [IA_schemaName],
|
|
'Criteria' as [IA_tableName],
|
|
'CR_code, CR_criteria_type' as [IA_columnsName],
|
|
0 as [IA_isClustered],
|
|
1 as [IA_isUnique],
|
|
NULL as [IA_includedColumns],
|
|
0 as [IA_isToBeRecreated],
|
|
0 as [IA_isToBeRecreated],
|
|
'NCIX_Criteria_COL_CR_code' as [IA_expectedIndexName],
|
|
NULL as [IA_foundIndexName],
|
|
0 as [IA_wasIndexFound],
|
|
0 as [IA_wasCreated],
|
|
0 as [IA_wasRenamed],
|
|
0 as [IA_wasDeleted],
|
|
0 as [IA_wasRecreated],
|
|
NULL as [IA_includedColumnsMatch],
|
|
NULL as [IA_actualColumns],
|
|
NULL as [IA_actualIncludedColumns],
|
|
NULL as [IA_statusMsg],
|
|
'STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, SORT_IN_TEMPDB = OFF, FILLFACTOR =90' as IA_index_options,
|
|
'OCTPDBA-414' AS [IA_reference]
|
|
WHERE NOT EXISTS(
|
|
SELECT 1
|
|
FROM [Arizona].[upd].[IA_IndexAlignementActions] s
|
|
WHERE [s].[IA_schemaName] = 'dbo'
|
|
AND [s].[IA_tableName] = 'Criteria'
|
|
AND [s].[IA_columnsName] = 'CR_code, CR_criteria_type'
|
|
AND [s].[IA_reference] = 'OCTPDBA-414'
|
|
)
|
|
|
|
/* CR_criteria_type */
|
|
INSERT INTO [Arizona].[upd].[IA_IndexAlignementActions]
|
|
(
|
|
[IA_executionDate],
|
|
[IA_schemaName],
|
|
[IA_tableName],
|
|
[IA_columnsName],
|
|
[IA_isClustered],
|
|
[IA_isUnique],
|
|
[IA_includedColumns],
|
|
[IA_isToBeDeleted],
|
|
[IA_isToBeRecreated],
|
|
[IA_expectedIndexName],
|
|
[IA_foundIndexName],
|
|
[IA_wasIndexFound],
|
|
[IA_wasCreated],
|
|
[IA_wasRenamed],
|
|
[IA_wasDeleted],
|
|
[IA_wasRecreated],
|
|
[IA_includedColumnsMatch],
|
|
[IA_actualColumns],
|
|
[IA_actualIncludedColumns],
|
|
[IA_statusMsg],
|
|
[IA_indexOptions],
|
|
[IA_reference]
|
|
)
|
|
SELECT
|
|
NULL as [IA_executionDate],
|
|
'dbo' as [IA_schemaName],
|
|
'Criteria' as [IA_tableName],
|
|
'CR_criteria_type' as [IA_columnsName],
|
|
0 as [IA_isClustered],
|
|
0 as [IA_isUnique],
|
|
NULL as [IA_includedColumns],
|
|
0 as [IA_isToBeRecreated],
|
|
0 as [IA_isToBeRecreated],
|
|
'NCIX_Criteria_COL_CR_criteria_type' as [IA_expectedIndexName],
|
|
NULL as [IA_foundIndexName],
|
|
0 as [IA_wasIndexFound],
|
|
0 as [IA_wasCreated],
|
|
0 as [IA_wasRenamed],
|
|
0 as [IA_wasDeleted],
|
|
0 as [IA_wasRecreated],
|
|
NULL as [IA_includedColumnsMatch],
|
|
NULL as [IA_actualColumns],
|
|
NULL as [IA_actualIncludedColumns],
|
|
NULL as [IA_statusMsg],
|
|
'STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, SORT_IN_TEMPDB = OFF, FILLFACTOR =90' as IA_index_options,
|
|
'OCTPDBA-414' AS [IA_reference]
|
|
WHERE NOT EXISTS(
|
|
SELECT 1
|
|
FROM [Arizona].[upd].[IA_IndexAlignementActions] s
|
|
WHERE [s].[IA_schemaName] = 'dbo'
|
|
AND [s].[IA_tableName] = 'Criteria'
|
|
AND [s].[IA_columnsName] = 'CR_criteria_type'
|
|
AND [s].[IA_reference] = 'OCTPDBA-414'
|
|
)
|
|
|
|
/* CR_master_ID */
|
|
INSERT INTO [Arizona].[upd].[IA_IndexAlignementActions]
|
|
(
|
|
[IA_executionDate],
|
|
[IA_schemaName],
|
|
[IA_tableName],
|
|
[IA_columnsName],
|
|
[IA_isClustered],
|
|
[IA_isUnique],
|
|
[IA_includedColumns],
|
|
[IA_isToBeDeleted],
|
|
[IA_isToBeRecreated],
|
|
[IA_expectedIndexName],
|
|
[IA_foundIndexName],
|
|
[IA_wasIndexFound],
|
|
[IA_wasCreated],
|
|
[IA_wasRenamed],
|
|
[IA_wasDeleted],
|
|
[IA_wasRecreated],
|
|
[IA_includedColumnsMatch],
|
|
[IA_actualColumns],
|
|
[IA_actualIncludedColumns],
|
|
[IA_statusMsg],
|
|
[IA_indexOptions],
|
|
[IA_reference]
|
|
)
|
|
SELECT
|
|
NULL as [IA_executionDate],
|
|
'dbo' as [IA_schemaName],
|
|
'Criteria' as [IA_tableName],
|
|
'CR_master_ID' as [IA_columnsName],
|
|
0 as [IA_isClustered],
|
|
0 as [IA_isUnique],
|
|
NULL as [IA_includedColumns],
|
|
0 as [IA_isToBeRecreated],
|
|
0 as [IA_isToBeRecreated],
|
|
'NCIX_Criteria_COL_CR_master_ID' as [IA_expectedIndexName],
|
|
NULL as [IA_foundIndexName],
|
|
0 as [IA_wasIndexFound],
|
|
0 as [IA_wasCreated],
|
|
0 as [IA_wasRenamed],
|
|
0 as [IA_wasDeleted],
|
|
0 as [IA_wasRecreated],
|
|
NULL as [IA_includedColumnsMatch],
|
|
NULL as [IA_actualColumns],
|
|
NULL as [IA_actualIncludedColumns],
|
|
NULL as [IA_statusMsg],
|
|
'STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, SORT_IN_TEMPDB = OFF, FILLFACTOR =90' as IA_index_options,
|
|
'OCTPDBA-414' AS [IA_reference]
|
|
WHERE NOT EXISTS(
|
|
SELECT 1
|
|
FROM [Arizona].[upd].[IA_IndexAlignementActions] s
|
|
WHERE [s].[IA_schemaName] = 'dbo'
|
|
AND [s].[IA_tableName] = 'Criteria'
|
|
AND [s].[IA_columnsName] = 'CR_master_ID'
|
|
AND [s].[IA_reference] = 'OCTPDBA-414'
|
|
)
|
|
|
|
/* CR_VGUID */
|
|
INSERT INTO [Arizona].[upd].[IA_IndexAlignementActions]
|
|
(
|
|
[IA_executionDate],
|
|
[IA_schemaName],
|
|
[IA_tableName],
|
|
[IA_columnsName],
|
|
[IA_isClustered],
|
|
[IA_isUnique],
|
|
[IA_includedColumns],
|
|
[IA_isToBeDeleted],
|
|
[IA_isToBeRecreated],
|
|
[IA_expectedIndexName],
|
|
[IA_foundIndexName],
|
|
[IA_wasIndexFound],
|
|
[IA_wasCreated],
|
|
[IA_wasRenamed],
|
|
[IA_wasDeleted],
|
|
[IA_wasRecreated],
|
|
[IA_includedColumnsMatch],
|
|
[IA_actualColumns],
|
|
[IA_actualIncludedColumns],
|
|
[IA_statusMsg],
|
|
[IA_indexOptions],
|
|
[IA_reference]
|
|
)
|
|
SELECT
|
|
NULL as [IA_executionDate],
|
|
'dbo' as [IA_schemaName],
|
|
'Criteria' as [IA_tableName],
|
|
'CR_VGUID' as [IA_columnsName],
|
|
0 as [IA_isClustered],
|
|
0 as [IA_isUnique],
|
|
NULL as [IA_includedColumns],
|
|
0 as [IA_isToBeRecreated],
|
|
0 as [IA_isToBeRecreated],
|
|
'NCIX_Criteria_COL_CR_VGUID' as [IA_expectedIndexName],
|
|
NULL as [IA_foundIndexName],
|
|
0 as [IA_wasIndexFound],
|
|
0 as [IA_wasCreated],
|
|
0 as [IA_wasRenamed],
|
|
0 as [IA_wasDeleted],
|
|
0 as [IA_wasRecreated],
|
|
NULL as [IA_includedColumnsMatch],
|
|
NULL as [IA_actualColumns],
|
|
NULL as [IA_actualIncludedColumns],
|
|
NULL as [IA_statusMsg],
|
|
'STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, SORT_IN_TEMPDB = OFF, FILLFACTOR =90' as IA_index_options,
|
|
'OCTPDBA-414' AS [IA_reference]
|
|
WHERE NOT EXISTS(
|
|
SELECT 1
|
|
FROM [Arizona].[upd].[IA_IndexAlignementActions] s
|
|
WHERE [s].[IA_schemaName] = 'dbo'
|
|
AND [s].[IA_tableName] = 'Criteria'
|
|
AND [s].[IA_columnsName] = 'CR_VGUID'
|
|
AND [s].[IA_reference] = 'OCTPDBA-414'
|
|
)
|
|
|
|
/* Criteria_ID */
|
|
INSERT INTO [Arizona].[upd].[IA_IndexAlignementActions]
|
|
(
|
|
[IA_executionDate],
|
|
[IA_schemaName],
|
|
[IA_tableName],
|
|
[IA_columnsName],
|
|
[IA_isClustered],
|
|
[IA_isUnique],
|
|
[IA_includedColumns],
|
|
[IA_isToBeDeleted],
|
|
[IA_isToBeRecreated],
|
|
[IA_expectedIndexName],
|
|
[IA_foundIndexName],
|
|
[IA_wasIndexFound],
|
|
[IA_wasCreated],
|
|
[IA_wasRenamed],
|
|
[IA_wasDeleted],
|
|
[IA_wasRecreated],
|
|
[IA_includedColumnsMatch],
|
|
[IA_actualColumns],
|
|
[IA_actualIncludedColumns],
|
|
[IA_statusMsg],
|
|
[IA_indexOptions],
|
|
[IA_reference]
|
|
)
|
|
SELECT
|
|
NULL as [IA_executionDate],
|
|
'dbo' as [IA_schemaName],
|
|
'Criteria' as [IA_tableName],
|
|
'Criteria_ID' as [IA_columnsName],
|
|
0 as [IA_isClustered],
|
|
1 as [IA_isUnique],
|
|
NULL as [IA_includedColumns],
|
|
0 as [IA_isToBeRecreated],
|
|
0 as [IA_isToBeRecreated],
|
|
'PK_Criteria_ID' as [IA_expectedIndexName],
|
|
NULL as [IA_foundIndexName],
|
|
0 as [IA_wasIndexFound],
|
|
0 as [IA_wasCreated],
|
|
0 as [IA_wasRenamed],
|
|
0 as [IA_wasDeleted],
|
|
0 as [IA_wasRecreated],
|
|
NULL as [IA_includedColumnsMatch],
|
|
NULL as [IA_actualColumns],
|
|
NULL as [IA_actualIncludedColumns],
|
|
NULL as [IA_statusMsg],
|
|
'STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, SORT_IN_TEMPDB = OFF, FILLFACTOR =90' as IA_index_options,
|
|
'OCTPDBA-414' AS [IA_reference]
|
|
WHERE NOT EXISTS(
|
|
SELECT 1
|
|
FROM [Arizona].[upd].[IA_IndexAlignementActions] s
|
|
WHERE [s].[IA_schemaName] = 'dbo'
|
|
AND [s].[IA_tableName] = 'Criteria'
|
|
AND [s].[IA_reference] = 'OCTPDBA-414'
|
|
AND [s].[IA_columnsName] = 'Criteria_ID'
|
|
)
|
|
|
|
|
|
|
|
|
|
|
|
EXEC [upd].[alignIndexes] @in_debug = 01, -- int
|
|
@in_dropUnreferencedIndexes = NULL; -- bit
|
|
|
|
SELECT *
|
|
FROM upd.IA_IndexAlignementActions;
|
|
|
|
ROLLBACK TRANSACTION; |