687 lines
30 KiB
PL/PgSQL
687 lines
30 KiB
PL/PgSQL
/*=============================================================================
|
|
|
|
Maintain and align indexes on Arizona.dbo.entry
|
|
|
|
TODO:
|
|
allow to skip INDEX DROP for unlisted indexes
|
|
|
|
Parameters
|
|
----------------------
|
|
Populate the var table @indexesToMaintains to drop / create and rename indexes to be aligned.
|
|
You need to specify those fields:
|
|
* schemaName, tableName: the schema and table name for which you want to maintain the indexes.
|
|
This script was written with working against several tables in mind, but is not (yet) tested to handle this case.
|
|
* columnsName: What columns are part of the index key, in the correct order
|
|
As we have divergent indexes names, we must use the index columns as the key to find the indexes.
|
|
* includedColumns_inp: if the index have included columns, list them here.
|
|
When an index is found, the included columns list it also compared to determine if the index is aligned.
|
|
* expectedIndexName_inp: The name the index should have. It will be used when creating missing indexes or renaming existing indexes
|
|
The index name is optionnal if we specify in the table that the index must be dropped.
|
|
* isToBeDeleted_inp: if true, indicate that the index should be dropped.
|
|
* isToBeRecreated_inp: if true, will drop and re-create the index even if the structure matches.
|
|
* isClustered_inp: if true and the index must be (re-)created, it will be created as a clustered index
|
|
* isUnique_inp: if true and the index must be (re-)created, it will be created as a unique index
|
|
|
|
Note that:
|
|
* Matching is always made from the columns of the index, never on the name of the index.
|
|
The reason is that we might have difference in the name of the index between several servers.
|
|
* If an index exists with the name A, but your list contains a similar index with the name B, index A will be renamed to B with sp_rename
|
|
* For each table(s) referenced in @indexesToMaintains, if an index exists that is not listed in the variable table it will be considered to be
|
|
dropped if @DoDropExistingIndexesNotReferenced is set to 1.
|
|
* If you list a specific existing index to be dropped in @indexesToMaintains then the following fields are optionnal:
|
|
expected index name
|
|
flag isClustered
|
|
flag isUnique
|
|
|
|
Context
|
|
----------------------
|
|
In any databases
|
|
|
|
Creation : 09.11.2022 / TSC
|
|
Modifications:
|
|
|
|
=============================================================================*/
|
|
USE [Arizona];
|
|
GO
|
|
|
|
BEGIN TRANSACTION;
|
|
SET XACT_ABORT ON;
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @query VARCHAR(MAX); /* for dynamic sql */
|
|
DECLARE @schemaName VARCHAR(100); /* used in cursor */
|
|
DECLARE @tableName VARCHAR(100); /* used in cursor */
|
|
|
|
/*
|
|
If set to true, the script will not apply the change.
|
|
Generated statements are always printed on the message tab.
|
|
*/
|
|
DECLARE @DoOnlyOutputStatement BIT = 0;
|
|
|
|
/*
|
|
If the script find local indexes that are not listed in the variable table, should it drop them or not ?
|
|
An index whose definition is not aligned with the temp table or flagged "to be rebuilt" will always be dropped before creation.
|
|
*/
|
|
DECLARE @DoDropExistingIndexesNotReferenced BIT = 0;
|
|
|
|
DECLARE @indexesToMaintains TABLE(
|
|
[Id] INT IDENTITY NOT NULL
|
|
,[SchemaName] VARCHAR(100) NOT NULL /* the schema the table is part of */
|
|
,[TableName] VARCHAR(100) NOT NULL /* On which table is the index, whithout the schema */
|
|
,columnsName_inp VARCHAR(MAX) NOT NULL /* list all the columns of the index, in the correct orders */
|
|
,isClustered_inp BIT NOT NULL DEFAULT 0 /* Is the indexe a clustered index ? */
|
|
,isUnique_inp BIT NOT NULL DEFAULT 0 /* Is the index unique ? */
|
|
,includedColumns_inp VARCHAR(MAX)NULL /* list all the included columns the index should have. only relevant for idx not to be dropped */
|
|
,isToBeDeleted_inp BIT NOT NULL DEFAULT 0 /* must the script drop this index ? */
|
|
,isToBeRecreated_inp BIT NOT NULL DEFAULT 0 /* must the script recreate this index ?
|
|
If an existing index differs from the definition, it will be dropped and the correct one created.
|
|
Use this only to force the re-creation even if the structure is identical */
|
|
,expectedIndexName_inp VARCHAR(MAX) NULL /* the name the index should have, used to determine if a rename of the existing index should be done */
|
|
,indexName VARCHAR(MAX) NULL /* computed by the script. The index name on this system, as indexes name can be different accross systems*/
|
|
,isIndexFound_comp BIT NOT NULL DEFAULT 0 /* computed by the script. set to 1 if the index is present on the server */
|
|
,isToBeCreated_comp BIT NULL DEFAULT 0 /* computed by the script. does this index will be created on the server ? */
|
|
,isToBeRenamed_comp BIT NULL DEFAULT 0 /* computed by the script. does this index will be renamed ? */
|
|
,isToBeDeleted_comp BIT NULL DEFAULT 0 /* computed by the script. does this index will be deleted ? */
|
|
,isToBeRecreated_comp BIT NULL DEFAULT 0 /* computed by the script. does this index will be re-created ? */
|
|
,internal_includedColumnsMatch BIT NULL /* computed ba the script. Are the expected and actual included columns identical ? */
|
|
,internal_actualColumns VARCHAR(MAX) NULL /* the actual columns in the existing index */
|
|
,internal_actualIncludedColumns VARCHAR(MAX) NULL /* the actual columns in the INCLUDE part of the index */
|
|
,internal_statusMsg VARCHAR(MAX) /* Message, used for debug */
|
|
);
|
|
|
|
/* Declare the expected structure of the indexes */
|
|
INSERT INTO @indexesToMaintains ([columnsName_inp],
|
|
[isToBeDeleted_inp],
|
|
[isToBeRecreated_inp],
|
|
[expectedIndexName_inp],
|
|
[includedColumns_inp],
|
|
[schemaName],
|
|
[tableName],
|
|
[isClustered_inp],
|
|
[isUnique_inp])
|
|
/* indexe(s) we do want */
|
|
VALUES('Entry_id',0,0,'PK_Entry_id', NULL,'dbo','entry',0,0)
|
|
,('ET_batch_run',0,0,'NCIX_Entry_COL_ET_batch_run',NULL,'dbo','entry',0,0)
|
|
,('ET_accounting_period',0,0,'NCIX_Entry_COL_ET_accounting_period','Entry_ID','dbo','entry',0,0)
|
|
,('ET_document_header',0,0,'NCIX_Entry_COL_ET_document_header','ET_debit_currency_amount, ET_credit_currency_amount','dbo','entry',0,0)
|
|
,('ET_account, ET_document_header',0,0,'NCIX_Entry_COL_ET_account','Entry_ID','dbo','entry',0,0)
|
|
,('ET_reconciliation_status, ET_account, ET_document_header',0,0,'NCIX_Entry_COL_ET_reconciliation_status','ET_debit_base_amount, ET_credit_base_amount, ET_reconciliation_base_amount','dbo','entry',0,0)
|
|
,('ET_predefined_entry',0,0,'NCIX_Entry_COL_ET_predefined_entry',NULL,'dbo','entry',0,0)
|
|
,('ET_entry_address',0,0,'NCIX_Entry_COL_ET_entry_address',NULL,'dbo','entry',0,0)
|
|
,('ET_currency',0,0,'NCIX_Entry_COL_ET_currency',NULL,'dbo','entry',0,0)
|
|
|
|
/* indexe(s) used in central, even if not in pharmacies*/
|
|
,('ET_entry_type',0,0,'NCIX_Entry_COL_ET_entry_type',NULL,'dbo','entry',0,0)
|
|
|
|
/* indexe(s) we really want to drop*/
|
|
,('ET_master_ID',1,0,'NCIX_Entry_COL_ET_master_ID',NULL,'dbo','entry',0,0)
|
|
,('ET_bmc_user_profile',1,0,'NCIX_Entry_COL_ET_bmc_user_profile',NULL,'dbo','entry',0,0)
|
|
,('ET_sales_tax_code',1,0,'NCIX_Entry_COL_ET_sales_tax_code',NULL,'dbo','entry',0,0)
|
|
,('ET_APS_TS', 1, 0, 'NCIX_Entry_COL_ET_APS_TS',NULL,'dbo','entry',0,0)
|
|
|
|
;
|
|
|
|
/* check for duplicate */
|
|
DECLARE @msg VARCHAR(MAX) = '';
|
|
SELECT @msg = @msg + 'Duplicate index (on expected index name) found in indexes definition. Table ['+[itm].[SchemaName]+'].['+[itm].[TableName]+'], expected index name ['+[itm].[expectedIndexName_inp]+']'+CHAR(13)+CHAR(10)
|
|
FROM @indexesToMaintains [itm]
|
|
JOIN (
|
|
SELECT
|
|
[ii].[expectedIndexName_inp] AS [key]
|
|
FROM @indexesToMaintains [ii]
|
|
GROUP BY [ii].[expectedIndexName_inp]
|
|
HAVING COUNT(1)>1
|
|
) d ON d.[key] = [itm].[expectedIndexName_inp]
|
|
GROUP BY [itm].[SchemaName], [itm].[TableName], [itm].[expectedIndexName_inp]
|
|
;
|
|
|
|
/* stop here if we have an issue in the variable table */
|
|
IF @msg <> ''
|
|
BEGIN
|
|
RAISERROR(@msg, 16, 4);
|
|
IF @@TRANCOUNT>0
|
|
BEGIN
|
|
ROLLBACK TRANSACTION;
|
|
END
|
|
RETURN
|
|
END
|
|
|
|
SET @msg = '';
|
|
SELECT @msg = @msg + 'Duplicate index (on column definition) found in indexes definition. Table ['+[itm].[SchemaName]+'].['+[itm].[TableName]+'], columns in index: '+[itm].[columnsName_inp]
|
|
+ CASE
|
|
WHEN [itm].[includedColumns_inp] IS NOT NULL THEN ', included colum(s): '+[itm].[includedColumns_inp]
|
|
ELSE ''
|
|
END
|
|
+CHAR(13)+CHAR(10)
|
|
FROM @indexesToMaintains [itm]
|
|
JOIN (
|
|
SELECT
|
|
[ii].[columnsName_inp]+'_'+ISNULL([ii].[includedColumns_inp],'') AS [key]
|
|
FROM @indexesToMaintains [ii]
|
|
GROUP BY [ii].[columnsName_inp]+'_'+ISNULL([ii].[includedColumns_inp],'')
|
|
HAVING COUNT(1)>1
|
|
) d ON d.[key] = [itm].[columnsName_inp]+'_'+ISNULL([itm].[includedColumns_inp],'')
|
|
GROUP BY [itm].[SchemaName], [itm].[TableName], [itm].[columnsName_inp], [itm].[includedColumns_inp]
|
|
;
|
|
|
|
/* stop here if we have an issue in the variable table */
|
|
IF @msg <> ''
|
|
BEGIN
|
|
RAISERROR(@msg, 16, 4);
|
|
IF @@TRANCOUNT>0
|
|
BEGIN
|
|
ROLLBACK TRANSACTION;
|
|
END
|
|
RETURN
|
|
END
|
|
|
|
/* fetch the indexes name on the current instance */
|
|
DECLARE curIdxName CURSOR FAST_FORWARD READ_ONLY FOR
|
|
SELECT DISTINCT [itm].[schemaName], [itm].[tableName]
|
|
FROM @indexesToMaintains [itm];
|
|
|
|
OPEN curIdxName;
|
|
|
|
FETCH NEXT FROM curIdxName INTO @schemaName, @tableName;
|
|
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
|
|
;WITH ColInfo
|
|
AS (
|
|
SELECT
|
|
o.[Name] AS TblName
|
|
,s.[Name] + '.' + o.[Name] AS SchemaTbl
|
|
,i.[Name] AS IndexName
|
|
,i.[is_primary_key] AS IsPrimaryKey
|
|
,i.[is_unique_constraint] AS IsUniqueConstraint
|
|
,i.[is_unique] AS IsUnique
|
|
,c.[Name] AS ColName
|
|
,c.[is_computed] AS IsComputedCol
|
|
,ic.[is_included_column] AS IsIncludedCol
|
|
,ic.[key_ordinal]
|
|
,i.[filter_definition] AS FilterDefinition
|
|
FROM [sys].[objects] o
|
|
INNER JOIN [sys].[schemas] s
|
|
ON o.SCHEMA_ID = s.SCHEMA_ID
|
|
INNER JOIN [sys].[COLUMNS] c
|
|
ON o.OBJECT_ID = c.OBJECT_ID
|
|
INNER JOIN [sys].[indexes] i
|
|
ON c.OBJECT_ID = i.OBJECT_ID
|
|
INNER JOIN [sys].[index_columns] ic
|
|
ON i.[index_id] = ic.[index_id]
|
|
AND o.OBJECT_ID = ic.OBJECT_ID
|
|
AND c.[column_id] = ic.[column_id]
|
|
)
|
|
|
|
/* map the list to the actual indexes */
|
|
UPDATE [itm] SET [itm].[indexName] = i.[name]
|
|
,[itm].[internal_includedColumnsMatch] = CASE
|
|
WHEN ISNULL([itm].[includedColumns_inp],'') = ISNULL([idx_cols].[includedColumns],'') THEN 1
|
|
ELSE 0
|
|
END
|
|
,[itm].[internal_actualIncludedColumns] = [idx_cols].[includedColumns]
|
|
,[itm].[internal_actualColumns] = [idx_cols].[IndexColumns]
|
|
,[itm].[isIndexFound_comp] = 1
|
|
,[itm].[isToBeCreated_comp] = 0
|
|
FROM [sys].[indexes] AS i WITH (NOLOCK)
|
|
INNER JOIN [sys].[objects] o WITH (NOLOCK)
|
|
ON i.OBJECT_ID = o.OBJECT_ID
|
|
OUTER APPLY (
|
|
SELECT DISTINCT
|
|
x.TblName
|
|
,x.IndexName
|
|
,x.IsPrimaryKey
|
|
,x.IsUniqueConstraint
|
|
,x.IsUnique
|
|
,c.IndexColumns
|
|
,ci.includedColumns
|
|
,cc.ComputedColumns
|
|
,x.FilterDefinition
|
|
FROM ColInfo x
|
|
CROSS APPLY (
|
|
SELECT STUFF(sq.strXML, 1, 2, '') AS IndexColumns
|
|
FROM (
|
|
SELECT ', ' + ISNULL(x2.ColName, '')
|
|
FROM ColInfo x2
|
|
WHERE x.TblName = x2.TblName
|
|
AND x.IndexName = x2.IndexName
|
|
AND x2.IsIncludedCol = 0
|
|
ORDER BY x2.[key_ordinal]
|
|
FOR XML PATH('')
|
|
) sq(strXML)
|
|
) c
|
|
OUTER APPLY (
|
|
SELECT STUFF(sq.strXML, 1, 2, '') AS includedColumns
|
|
FROM (
|
|
SELECT ', ' + ISNULL(x2.ColName, '')
|
|
FROM ColInfo x2
|
|
WHERE x.TblName = x2.TblName
|
|
AND x.IndexName = x2.IndexName
|
|
AND x2.IsIncludedCol = 1
|
|
ORDER BY x2.[key_ordinal]
|
|
FOR XML PATH('')
|
|
) sq(strXML)
|
|
) ci
|
|
OUTER APPLY (
|
|
SELECT STUFF(sq.strXML, 1, 2, '') AS ComputedColumns
|
|
FROM (
|
|
SELECT ', ' + ISNULL(x2.ColName, '')
|
|
FROM ColInfo x2
|
|
WHERE x.TblName = x2.TblName
|
|
AND x.IndexName = x2.IndexName
|
|
AND x2.IsComputedCol = 1
|
|
ORDER BY x2.[key_ordinal]
|
|
FOR XML PATH('')
|
|
) sq(strXML)
|
|
) cc
|
|
WHERE x.TblName = o.[Name]
|
|
AND x.IndexName = i.[Name]
|
|
) idx_cols
|
|
LEFT OUTER JOIN (
|
|
SELECT
|
|
SCHEMA_NAME(tab.SCHEMA_ID) + '.' + tab.[Name] AS [table]
|
|
,col.[column_id]
|
|
,col.[Name] AS [COLUMN_NAME]
|
|
,CASE
|
|
WHEN fk.OBJECT_ID IS NOT NULL THEN '>-'
|
|
ELSE NULL
|
|
END AS rel
|
|
,SCHEMA_NAME(pk_tab.SCHEMA_ID) + '.' + pk_tab.[Name] AS primary_table
|
|
,pk_col.[Name] AS pk_column_name
|
|
,fk_cols.[constraint_column_id] AS NO
|
|
,fk.[Name] AS fk_constraint_name
|
|
,tab.[Name] AS tbl_name
|
|
FROM [sys].[TABLES] tab
|
|
INNER JOIN [sys].[COLUMNS] col
|
|
ON col.OBJECT_ID = tab.OBJECT_ID
|
|
LEFT OUTER JOIN [sys].[foreign_key_columns] fk_cols
|
|
ON fk_cols.[parent_object_id] = tab.OBJECT_ID
|
|
AND fk_cols.[parent_column_id] = col.[column_id]
|
|
LEFT OUTER JOIN [sys].[foreign_keys] fk
|
|
ON fk.OBJECT_ID = fk_cols.[constraint_object_id]
|
|
LEFT OUTER JOIN [sys].[TABLES] pk_tab
|
|
ON pk_tab.OBJECT_ID = fk_cols.[referenced_object_id]
|
|
LEFT OUTER JOIN [sys].[COLUMNS] pk_col
|
|
ON pk_col.[column_id] = fk_cols.[referenced_column_id]
|
|
AND pk_col.OBJECT_ID = fk_cols.[referenced_object_id]
|
|
WHERE fk.OBJECT_ID IS NOT NULL
|
|
) fk
|
|
ON fk.tbl_name = o.[Name]
|
|
AND idx_cols.IndexColumns LIKE '%' + fk.[COLUMN_NAME] + '%'
|
|
INNER JOIN @indexesToMaintains [itm]
|
|
ON itm.[schemaName] = @schemaName
|
|
AND itm.[tableName] = @tableName
|
|
AND itm.[columnsName_inp] COLLATE Latin1_General_CI_AI = [idx_cols].[IndexColumns] COLLATE Latin1_General_CI_AI
|
|
AND ISNULL([itm].[includedColumns_inp],'') COLLATE Latin1_General_CI_AI = ISNULL([idx_cols].[includedColumns],'') COLLATE Latin1_General_CI_AI
|
|
WHERE o.TYPE = 'U' -- user table
|
|
--AND i.[index_id] = s.[index_id]
|
|
--AND s.[database_id] = DB_ID()
|
|
AND o.[Name] = @tableName
|
|
AND SCHEMA_NAME(o.SCHEMA_ID) = @schemaName
|
|
OPTION (RECOMPILE);
|
|
|
|
|
|
/* Add the indexes not in the list but present on the server to the list */
|
|
;WITH ColInfo
|
|
AS (
|
|
SELECT
|
|
o.[Name] AS TblName
|
|
,SCHEMA_NAME(o.[schema_id]) AS SchemaName
|
|
,s.[Name] + '.' + o.[Name] AS SchemaTbl
|
|
,i.[Name] AS IndexName
|
|
,i.[is_primary_key] AS IsPrimaryKey
|
|
,i.[is_unique_constraint] AS IsUniqueConstraint
|
|
,i.[is_unique] AS IsUnique
|
|
,c.[Name] AS ColName
|
|
,c.[is_computed] AS IsComputedCol
|
|
,ic.[is_included_column] AS IsIncludedCol
|
|
,ic.[key_ordinal]
|
|
,i.[filter_definition] AS FilterDefinition
|
|
FROM [sys].[objects] o
|
|
INNER JOIN [sys].[schemas] s
|
|
ON o.SCHEMA_ID = s.SCHEMA_ID
|
|
INNER JOIN [sys].[COLUMNS] c
|
|
ON o.OBJECT_ID = c.OBJECT_ID
|
|
INNER JOIN [sys].[indexes] i
|
|
ON c.OBJECT_ID = i.OBJECT_ID
|
|
INNER JOIN [sys].[index_columns] ic
|
|
ON i.[index_id] = ic.[index_id]
|
|
AND o.OBJECT_ID = ic.OBJECT_ID
|
|
AND c.[column_id] = ic.[column_id]
|
|
)
|
|
|
|
INSERT INTO @indexesToMaintains (
|
|
[schemaName]
|
|
,[tableName]
|
|
,[columnsName_inp]
|
|
,[indexName]
|
|
,[isToBeDeleted_inp]
|
|
,[isToBeDeleted_comp]
|
|
,[isToBeRecreated_inp]
|
|
,[internal_statusMsg]
|
|
,[internal_actualIncludedColumns]
|
|
,[internal_actualColumns]
|
|
,[isIndexFound_comp]
|
|
,[isToBeCreated_comp]
|
|
,[expectedIndexName_inp]
|
|
)
|
|
SELECT DISTINCT
|
|
@schemaName AS [SchemaName]
|
|
,@tableName AS [TableName]
|
|
,c.[IndexColumns] AS [columnsName]
|
|
,x.[IndexName] AS indexName
|
|
,0 AS [isToBeDeleted_inp]
|
|
,CASE
|
|
WHEN EXISTS(
|
|
SELECT 1
|
|
FROM @indexesToMaintains [itm]
|
|
WHERE itm.[expectedIndexName_inp] = x.[IndexName]
|
|
OR ISNULL(itm.[columnsName_inp],'') <> ISNULL(itm.[internal_actualColumns],'')
|
|
OR ISNULL(itm.[includedColumns_inp],'') <> ISNULL(itm.[internal_actualIncludedColumns],'')
|
|
) THEN 1
|
|
WHEN @DoDropExistingIndexesNotReferenced = 1 THEN 1
|
|
ELSE 0
|
|
END AS [isToBeDeleted_comp]
|
|
,0 AS [isToBeRecreated_inp]
|
|
,CASE
|
|
WHEN EXISTS(
|
|
SELECT 1
|
|
FROM @indexesToMaintains [itm]
|
|
WHERE itm.[expectedIndexName_inp] = x.[IndexName]
|
|
OR ISNULL(itm.[columnsName_inp],'') <> ISNULL(itm.[internal_actualColumns],'')
|
|
OR ISNULL(itm.[includedColumns_inp],'') <> ISNULL(itm.[internal_actualIncludedColumns],'')
|
|
) THEN 'Index present locally not as expected, drop before creation'
|
|
WHEN @DoDropExistingIndexesNotReferenced = 1 THEN 'Index present locally only'
|
|
ELSE NULL
|
|
END AS [internal_statusMsg]
|
|
,ci.[includedColumns] AS [internal_actualIncludedColumns]
|
|
,c.[IndexColumns] AS [internal_actualColumns]
|
|
,1 AS [isIndexFound_comp]
|
|
,0 AS [isToBeCreated_comp]
|
|
,x.[IndexName] AS [expectedIndexName_inp]
|
|
FROM ColInfo x
|
|
CROSS APPLY(SELECT IndexColumns = STUFF(sq.strXML, 1, 2, '')
|
|
FROM ( SELECT ', ' + ISNULL(x2.ColName, '')
|
|
FROM ColInfo x2
|
|
WHERE x.TblName = x2.TblName AND x.IndexName = x2.IndexName AND x2.IsIncludedCol = 0
|
|
ORDER BY x2.key_ordinal
|
|
FOR XML PATH('')
|
|
) sq (strXML)
|
|
) c
|
|
Outer APPLY(SELECT IncludedColumns = STUFF(sq.strXML, 1, 2, '')
|
|
FROM ( SELECT ', ' + ISNULL(x2.ColName, '')
|
|
FROM ColInfo x2
|
|
WHERE x.TblName = x2.TblName AND x.IndexName = x2.IndexName AND x2.IsIncludedCol = 1
|
|
ORDER BY x2.key_ordinal
|
|
FOR XML PATH('')
|
|
) sq (strXML)
|
|
) ci
|
|
Outer APPLY(SELECT ComputedColumns = STUFF(sq.strXML, 1, 2, '')
|
|
FROM ( SELECT ', ' + ISNULL(x2.ColName, '')
|
|
FROM ColInfo x2
|
|
WHERE x.TblName = x2.TblName AND x.IndexName = x2.IndexName AND x2.IsComputedCol = 1
|
|
ORDER BY x2.key_ordinal
|
|
FOR XML PATH('')
|
|
) sq (strXML)
|
|
) cc
|
|
LEFT OUTER JOIN (
|
|
SELECT
|
|
SCHEMA_NAME(tab.SCHEMA_ID) + '.' + tab.[Name] AS [table]
|
|
,col.[column_id]
|
|
,col.[Name] AS [COLUMN_NAME]
|
|
,CASE
|
|
WHEN fk.OBJECT_ID IS NOT NULL THEN '>-'
|
|
ELSE NULL
|
|
END AS rel
|
|
,SCHEMA_NAME(pk_tab.SCHEMA_ID) + '.' + pk_tab.[Name] AS primary_table
|
|
,pk_col.[Name] AS pk_column_name
|
|
,fk_cols.[constraint_column_id] AS NO
|
|
,fk.[Name] AS fk_constraint_name
|
|
,tab.[Name] AS tbl_name
|
|
FROM [sys].[TABLES] tab
|
|
INNER JOIN [sys].[COLUMNS] col
|
|
ON col.OBJECT_ID = tab.OBJECT_ID
|
|
LEFT OUTER JOIN [sys].[foreign_key_columns] fk_cols
|
|
ON fk_cols.[parent_object_id] = tab.OBJECT_ID
|
|
AND fk_cols.[parent_column_id] = col.[column_id]
|
|
LEFT OUTER JOIN [sys].[foreign_keys] fk
|
|
ON fk.OBJECT_ID = fk_cols.[constraint_object_id]
|
|
LEFT OUTER JOIN [sys].[TABLES] pk_tab
|
|
ON pk_tab.OBJECT_ID = fk_cols.[referenced_object_id]
|
|
LEFT OUTER JOIN [sys].[COLUMNS] pk_col
|
|
ON pk_col.[column_id] = fk_cols.[referenced_column_id]
|
|
AND pk_col.OBJECT_ID = fk_cols.[referenced_object_id]
|
|
WHERE fk.OBJECT_ID IS NOT NULL
|
|
) fk
|
|
ON fk.tbl_name = x.[TblName]
|
|
AND [c].[IndexColumns] LIKE '%' + fk.[COLUMN_NAME] + '%'
|
|
WHERE x.[SchemaName] =@schemaName
|
|
AND x.[TblName] =@tableName
|
|
AND NOT EXISTS(
|
|
SELECT 1
|
|
FROM @indexesToMaintains [itm2]
|
|
WHERE 1=1
|
|
AND ISNULL(itm2.[internal_actualColumns],'') = ISNULL(c.[IndexColumns],'')
|
|
AND ISNULL(itm2.[internal_actualIncludedColumns],'') = ISNULL(ci.[includedColumns],'')
|
|
)
|
|
OPTION (RECOMPILE);
|
|
|
|
|
|
FETCH NEXT FROM curIdxName INTO @schemaName, @tableName;
|
|
END
|
|
|
|
CLOSE curIdxName;
|
|
DEALLOCATE curIdxName;
|
|
|
|
|
|
/* Adjust the "_comp" flags to create a matrix of actions */
|
|
/* to be created */
|
|
UPDATE [itm]
|
|
SET
|
|
[itm].[isToBeCreated_comp] = CASE
|
|
WHEN [itm].[isToBeDeleted_inp] = 0 THEN 1
|
|
ELSE 0
|
|
END
|
|
FROM @indexesToMaintains [itm]
|
|
WHERE [itm].[indexName] IS NULL /* index was not found on the server */
|
|
AND [itm].[expectedIndexName_inp] IS NOT NULL /* we did gave an index name, ie: we want to create it if missing */
|
|
;
|
|
|
|
/* to be renamed */
|
|
UPDATE [itm]
|
|
SET
|
|
[itm].[isToBeRenamed_comp] = 1
|
|
FROM @indexesToMaintains [itm]
|
|
WHERE [itm].[indexName] IS NOT NULL /* index was not found on the server */
|
|
AND [itm].[expectedIndexName_inp] IS NOT NULL /* we did gave an index name, ie: we expect that index to be present */
|
|
AND [indexName] <> [itm].[expectedIndexName_inp]
|
|
AND [isToBeDeleted_inp] = 0
|
|
AND [isToBeDeleted_comp] = 0
|
|
;
|
|
|
|
/* to be re-created */
|
|
UPDATE [itm]
|
|
SET
|
|
[itm].[isToBeRecreated_comp] = 1
|
|
FROM @indexesToMaintains [itm]
|
|
WHERE [isToBeRecreated_inp] = 1
|
|
;
|
|
|
|
/* to be deleted, because specified in table */
|
|
UPDATE itm
|
|
SET
|
|
[itm].[isToBeDeleted_comp] = CASE
|
|
WHEN [itm].[isIndexFound_comp] = 0 THEN 0
|
|
ELSE 1
|
|
END
|
|
,[itm].[isToBeRecreated_comp] = 0 /* deletion override re-create */
|
|
,[itm].[isToBeRenamed_comp] = 0 /* deletion override rename */
|
|
FROM @indexesToMaintains [itm]
|
|
WHERE [itm].[isToBeDeleted_inp] = 1
|
|
;
|
|
|
|
/* to be deleted, because different to specs. link on index name */
|
|
UPDATE [exp]
|
|
SET
|
|
[exp].[isToBeCreated_comp] = 1 /* flag the expected index "to be created" */
|
|
FROM @indexesToMaintains [exp] --expected
|
|
JOIN @indexesToMaintains [act] --actual
|
|
ON [act].[indexName] = [exp].[expectedIndexName_inp]
|
|
WHERE [exp].[isToBeDeleted_comp] = 0
|
|
AND [exp].[isToBeCreated_comp] = 0
|
|
AND [exp].[isToBeRenamed_comp] = 0
|
|
AND [exp].[isToBeRecreated_inp] = 0
|
|
AND (
|
|
[exp].[columnsName_inp] != [act].[internal_actualColumns]
|
|
OR [exp].[includedColumns_inp] != [act].[internal_actualIncludedColumns]
|
|
)
|
|
;
|
|
|
|
UPDATE [act]
|
|
SET
|
|
[act].[isToBeDeleted_comp] = 1 /* flag the actual index "to be deleted" */
|
|
FROM @indexesToMaintains [exp] --expected
|
|
JOIN @indexesToMaintains [act] --actual
|
|
ON [act].[indexName] = [exp].[expectedIndexName_inp]
|
|
WHERE (
|
|
[exp].[columnsName_inp] != [act].[internal_actualColumns]
|
|
OR [exp].[includedColumns_inp] != [act].[internal_actualIncludedColumns]
|
|
)
|
|
;
|
|
|
|
/* to be deleted, because different to specs. link on index columns */
|
|
UPDATE [exp]
|
|
SET
|
|
[exp].[isToBeCreated_comp] = 1 /* flag the expected index "to be created" */
|
|
FROM @indexesToMaintains [exp] --expected
|
|
JOIN @indexesToMaintains [act] --actual
|
|
ON [act].[internal_actualColumns] = [exp].[columnsName_inp]
|
|
WHERE (
|
|
[exp].[columnsName_inp] != [act].[internal_actualColumns]
|
|
OR [exp].[includedColumns_inp] != [act].[internal_actualIncludedColumns]
|
|
)
|
|
;
|
|
UPDATE [act]
|
|
SET
|
|
[act].[isToBeDeleted_comp] = 1 /* flag the actual index "to be deleted" */
|
|
FROM @indexesToMaintains [exp] --expected
|
|
JOIN @indexesToMaintains [act] --actual
|
|
ON [act].[internal_actualColumns] = [exp].[columnsName_inp]
|
|
WHERE (
|
|
[exp].[columnsName_inp] != [act].[internal_actualColumns]
|
|
OR [exp].[includedColumns_inp] != [act].[internal_actualIncludedColumns]
|
|
)
|
|
;
|
|
|
|
/* if we have both "rename" and "delete" actions, remove the "rename" action */
|
|
UPDATE @indexesToMaintains
|
|
SET [isToBeRenamed_comp] = 0
|
|
WHERE [isToBeRenamed_comp] = 1
|
|
AND [isToBeDeleted_comp] = 1
|
|
;
|
|
|
|
/* ensure that we don't have clash between indexes to create and indexes to rename */
|
|
UPDATE ir
|
|
SET [ir].[isToBeRenamed_comp] = 0 /* do not rename this index */
|
|
, [ir].[isToBeDeleted_inp] = 1 /* drop this index instead, as we have one with the correct structure about to be created */
|
|
FROM @indexesToMaintains [ic] /* to create */
|
|
JOIN @indexesToMaintains [ir] /* to rename */
|
|
ON ic.[expectedIndexName_inp] = ir.[expectedIndexName_inp]
|
|
WHERE [ic].[isToBeCreated_comp] = 1
|
|
AND [ir].[isToBeRenamed_comp] = 1
|
|
|
|
/* Show the action matrix */
|
|
SELECT [itm].[isIndexFound_comp]
|
|
,[itm].[isToBeCreated_comp]
|
|
,[itm].[isToBeRenamed_comp]
|
|
,[itm].[isToBeDeleted_comp]
|
|
,[itm].[isToBeRecreated_comp]
|
|
,[itm].[internal_statusMsg]
|
|
,'....'
|
|
,[itm].[isToBeDeleted_inp]
|
|
,[itm].[isToBeRecreated_inp]
|
|
,[itm].[indexName]
|
|
,[itm].[expectedIndexName_inp]
|
|
,[itm].[columnsName_inp]
|
|
,[itm].[internal_actualColumns]
|
|
,[itm].[includedColumns_inp]
|
|
,[itm].[internal_actualIncludedColumns]
|
|
FROM @indexesToMaintains [itm]
|
|
ORDER BY [itm].[isIndexFound_comp],[itm].[isToBeDeleted_inp], [itm].[columnsName_inp];
|
|
|
|
|
|
/* Drop the indexe(s) that are to be deleted or recreated */
|
|
SET @query='';
|
|
SELECT @query = @query + 'DROP INDEX '+[itm].[indexName]+' ON dbo.entry;'+CHAR(13)+CHAR(10)
|
|
FROM @indexesToMaintains [itm]
|
|
WHERE [itm].[isToBeDeleted_comp] = 1
|
|
OR [itm].[isToBeRecreated_comp] = 1
|
|
;
|
|
|
|
PRINT REPLICATE('-', 40);
|
|
PRINT '-- execute DROP INDEX';
|
|
PRINT REPLICATE('-', 40);
|
|
PRINT @query;
|
|
PRINT REPLICATE('-', 40);
|
|
PRINT CHAR(13)+CHAR(10);
|
|
|
|
IF @DoOnlyOutputStatement = 0
|
|
BEGIN
|
|
EXECUTE(@query);
|
|
END
|
|
|
|
/* rename existing indexe(s) */
|
|
SET @query = '';
|
|
SELECT @query = @query + 'EXEC sp_rename @objname ='''+[itm].[tableName]+'.'+[itm].[indexName]+''', @newname = '''+[itm].[expectedIndexName_inp]+''';'+CHAR(13)+CHAR(10)
|
|
FROM @indexesToMaintains [itm]
|
|
WHERE [itm].[isToBeRenamed_comp] = 1
|
|
;
|
|
|
|
PRINT REPLICATE('-', 40);
|
|
PRINT '-- execute sp_rename';
|
|
PRINT REPLICATE('-', 40);
|
|
PRINT @query;
|
|
PRINT REPLICATE('-', 40);
|
|
PRINT CHAR(13)+CHAR(10);
|
|
|
|
IF @DoOnlyOutputStatement = 0
|
|
BEGIN
|
|
EXECUTE(@query);
|
|
END
|
|
|
|
/* create indexe(s) */
|
|
SET @query='';
|
|
SELECT @query = @query + 'CREATE '
|
|
+CASE WHEN [itm].[isClustered_inp]=1 THEN 'CLUSTERED' ELSE 'NONCLUSTERED' END
|
|
+CASE WHEN [itm].[isUnique_inp]=1 THEN ' UNIQUE' ELSE '' END
|
|
+' INDEX '+[itm].[expectedIndexName_inp]
|
|
+' ON '+[itm].[schemaName]+'.'+[itm].[tableName]+'('+[itm].[columnsName_inp]+')'
|
|
+ CASE
|
|
WHEN [itm].[includedColumns_inp] IS NOT NULL THEN ' INCLUDE ('+[itm].[includedColumns_inp]+')'
|
|
ELSE ''
|
|
END
|
|
+';'
|
|
+CHAR(13)+CHAR(10)
|
|
FROM @indexesToMaintains [itm]
|
|
WHERE (
|
|
[itm].[isToBeCreated_comp] = 1
|
|
OR [itm].[isToBeRecreated_comp] = 1
|
|
)
|
|
;
|
|
|
|
PRINT REPLICATE('-', 40);
|
|
PRINT '-- execute CREATE INDEX';
|
|
PRINT REPLICATE('-', 40);
|
|
PRINT @query;
|
|
PRINT REPLICATE('-', 40);
|
|
PRINT CHAR(13)+CHAR(10);
|
|
|
|
IF @DoOnlyOutputStatement = 0
|
|
BEGIN
|
|
EXECUTE(@query);
|
|
END
|
|
|
|
--ROLLBACK TRANSACTION;
|
|
COMMIT TRANSACTION
|