Отримання скриптів для створення всіх індексів певної таблиці БД

WITH IndexInfo AS ( SELECT ix.object_id , ix.NAME AS IndexName , ix.type_desc , ix.filter_definition , ix.is_unique , ix.is_primary_key , ix.allow_row_locks , ix.allow_page_locks , ds.NAME AS DataSpaceName , ds.type AS DataSpaceType , ix.is_padded , object_schema_name(ix.object_id) AS SchemaName , object_name(ix.object_id) AS TableName , IIF(ix.type <= 2, is_included_column, 0) AS HasIncludedColumn , IIF(ix.type in (5, 6), 1, 0) AS IsColumnStore , ( SELECT KeyColumns FROM ( SELECT IC2.object_id , IC2.index_id , STUFF(( SELECT ' , ' + C.NAME + IIF(MAX(CONVERT(INT, IC1.is_descending_key)) = 1 AND ix.type <= 2, ' DESC ', ' ') FROM sys.index_columns IC1 JOIN sys.columns C ON C.object_id = IC1.object_id AND C.column_id = IC1.column_id --AND IC1.is_included_column = 0 AND IIF(ix.type <= 2, IC1.is_included_column, 0) = 0 AND IIF(ix.type <= 2, IC1.key_ordinal, ic.index_column_id) > 0 WHERE IC1.object_id = IC2.object_id AND IC1.index_id = IC2.index_id GROUP BY IC1.object_id , C.NAME , index_id , IC1.key_ordinal , IC1.index_column_id ORDER BY IIF(ix.type <= 2, IC1.key_ordinal, IC1.index_column_id) FOR XML PATH('') ), 1, 2, '') KeyColumns FROM sys.index_columns IC2 GROUP BY IC2.object_id , IC2.index_id ) tmp WHERE tmp.object_id = ix.object_id AND tmp.index_id = ix.index_id ) AS KeyColumnsStr , ( SELECT IncludedColumns FROM ( SELECT IC2.object_id, IC2.index_id , STUFF(( SELECT ' , ' + C.NAME FROM sys.index_columns IC1 JOIN sys.columns C ON C.object_id = IC1.object_id AND C.column_id = IC1.column_id --AND IC1.is_included_column = 1 AND IIF(ix.type <= 2, IC1.is_included_column, 0) <> 0 WHERE IC1.object_id = IC2.object_id AND IC1.index_id = IC2.index_id --and IIF(ix.type <= 2, IC1.key_ordinal, ic.index_column_id) > 0 GROUP BY IC1.object_id, C.NAME, index_id, IC1.key_ordinal, IC1.index_column_id ORDER BY IIF(ix.type <= 2, IC1.key_ordinal, IC1.index_column_id) FOR XML PATH('') ), 1, 2, '') IncludedColumns FROM sys.index_columns IC2 GROUP BY IC2.object_id, IC2.index_id ) tmp WHERE tmp.object_id = ix.object_id AND tmp.index_id = ix.index_id AND IncludedColumns IS NOT NULL ) AS IncludedColumnsStr FROM sys.indexes ix INNER JOIN sys.index_columns ic ON ic.index_id = ix.index_id AND ic.object_id = ix.object_id INNER JOIN sys.columns col ON col.column_id = ic.column_id AND col.object_id = ix.object_id INNER JOIN sys.data_spaces ds ON ix.data_space_id = ds.data_space_id WHERE ix.NAME IS NOT NULL AND IIF(ix.type <= 2, ic.key_ordinal, ic.index_column_id) > 0 ) , Scrpt AS ( SELECT * , 'CREATE ' + CASE WHEN is_unique = 1 THEN ' UNIQUE ' ELSE '' END + type_desc COLLATE DATABASE_DEFAULT + ' INDEX ' + IndexName + ' ON ' + SchemaName + '.' + TableName + '(' + KeyColumnsStr + ')' + ISNULL(IIF(HasIncludedColumn > 0, ' ', ' INCLUDE (' + IncludedColumnsStr + ')'), '') + IIF(filter_definition IS NULL, ' ', ' WHERE ' + filter_definition) + ' WITH (' + CASE WHEN IsColumnStore = 1 THEN ' DROP_EXISTING = OFF ' ELSE IIF(is_padded = 1, ' PAD_INDEX = ON ', ' PAD_INDEX = OFF ') + ',' + ' DROP_EXISTING = OFF ' + ',' + ' ONLINE = OFF ' + ',' + IIF(allow_row_locks = 1, ' ALLOW_ROW_LOCKS = ON ', ' ALLOW_ROW_LOCKS = OFF ') + ',' + IIF(allow_page_locks = 1, ' ALLOW_PAGE_LOCKS = ON ', ' ALLOW_PAGE_LOCKS = OFF ') END + ' ) ' + IIF(DataSpaceType = 'FG','ON [' + DataSpaceName + ']', '') AS CreateIndexScript FROM IndexInfo ) SELECT DISTINCT IndexName, CreateIndexScript FROM Scrpt WHERE object_id = object_id('dbo.SWPartition')

Andriy Kravchenko

Admin, Writer, File Uploader

10.11.2024 16:14:38