Отримання скриптів для створення всіх індексів певної таблиці БД
Цей скрипт дозволяє отримати скрипти для створення індексів певної таблиці. Особливо зручно для перестворення таблиці, або копіювання індексів з одного екземпляру бази даних в інший.
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