SQL Server查找索引定义我的任务是将一组表架构、内容和表对象迁移到Azure SQLdb。我遇到了一个触发器和索引的问题。它们没有被复制。触发器很容易从sys.modules表中获取，但在索引方面却不太好。
我在这里或互联网上找不到任何东西，所以我写了这篇文章。我希望这能帮助到别人。
/*
========================WARNING!!!!!!!!================================
The follow has the following defaults that may not suite your needs.
PRIMARY KEY CLUSTERED
STATISTICS_NORECOMPUTE = OFF
SORT_IN_TEMPDB = OFF I leave this on Since my TempDB has the room,
bandwidth, and it's own IO channel.
It's up to you though.
ONLINE = OFF
Created ON [PRIMARY]
Created by: Frank Canapa
Date 2020-07-24
Based on the work of others on the interwebs
*/
;WITH MyCTE
AS
(
SELECT T = t.[name]
,A = 'ALTER TABLE [' + schema_name(t.schema_id) + '].[' + t.[name]+'] ADD CONSTRAINT [' + i.[name] + '] PRIMARY KEY CLUSTERED (' + substring(column_names, 1, len(column_names)-1)
, B =')WITH (PAD_INDEX = ' +
CASE WHEN i.[is_padded] = 0 THEN 'OFF'
WHEN i.[is_padded] = 1 THEN 'ON'
END
, C =', STATISTICS_NORECOMPUTE = OFF' + ', SORT_IN_TEMPDB = OFF' + ', IGNORE_DUP_KEY = ' +
CASE WHEN i.[IGNORE_DUP_KEY] = 0 THEN 'OFF'
WHEN i.[IGNORE_DUP_KEY] = 1 THEN 'ON'
END
, D = ', ONLINE = OFF' + ', ALLOW_ROW_LOCKS = ' +
CASE WHEN i.[ALLOW_ROW_LOCKS] = 0 THEN 'OFF'
WHEN i.[ALLOW_ROW_LOCKS] = 1 THEN 'ON'
END
, E = ', ALLOW_PAGE_LOCKS = ' +
CASE WHEN i.[ALLOW_PAGE_LOCKS] = 0 THEN 'OFF'
WHEN i.[ALLOW_PAGE_LOCKS] = 1 THEN 'ON'
END
, F = CASE WHEN i.[fill_factor] = 0 THEN ''
WHEN i.[fill_factor] > 0 THEN ', FILLFACTOR = ' + CAST(i.[fill_factor] AS varchar(3))
END
, G = ') ON [PRIMARY]'
FROM sys.objects t
INNER JOIN sys.indexes i
ON t.object_id = i.object_id
CROSS APPLY(SELECT col.[name] + ', '
FROM sys.index_columns ic
INNER JOIN sys.columns col
ON ic.object_id = col.object_id
AND ic.column_id = col.column_id
WHERE ic.object_id = t.object_id
AND ic.index_id = i.index_id
ORDER by key_ordinal
for xml path ('') ) D (column_names)
WHERE i.[is_primary_key] = 1
AND t.is_ms_shipped <> 1
AND index_id > 0
UNION
SELECT T = t.[name]
,A = 'CREATE ' +
CASE WHEN i.[type] = 1 THEN 'CLUSTERED'
WHEN i.[type] = 2 THEN 'NONCLUSTERED'
WHEN i.[type] = 3 THEN 'XML'
WHEN i.[type] = 4 THEN 'Spatial '
WHEN i.[type] = 5 THEN 'Clustered columnstore'
WHEN i.[type] = 6 THEN 'Nonclustered columnstore'
WHEN i.[type] = 7 THEN 'Nonclustered hash'
END
, B = ' INDEX [' + i.[name] + '] ON ['+ schema_name(t.schema_id) + '].[' + t.[name]+'] ('+substring(column_names, 1, len(column_names)-1) + ')WITH (PAD_INDEX = ' +
CASE WHEN i.[is_padded] = 0 THEN 'OFF'
WHEN i.[is_padded] = 1 THEN 'ON'
END
, C = ', STATISTICS_NORECOMPUTE = OFF' + ', SORT_IN_TEMPDB = OFF' + ', IGNORE_DUP_KEY = ' +
CASE WHEN i.[IGNORE_DUP_KEY] = 0 THEN 'OFF'
WHEN i.[IGNORE_DUP_KEY] = 1 THEN 'ON'
END
, D = ', ONLINE = OFF' + ', ALLOW_ROW_LOCKS = ' +
CASE WHEN i.[ALLOW_ROW_LOCKS] = 0 THEN 'OFF'
WHEN i.[ALLOW_ROW_LOCKS] = 1 THEN 'ON'
END
, E = ', ALLOW_PAGE_LOCKS = ' +
CASE WHEN i.[ALLOW_PAGE_LOCKS] = 0 THEN 'OFF'
WHEN i.[ALLOW_PAGE_LOCKS] = 1 THEN 'ON'
END
, F = CASE WHEN i.[fill_factor] = 0 THEN ''
WHEN i.[fill_factor] > 0 THEN ', FILLFACTOR = ' + CAST(i.[fill_factor] AS varchar(3))
END
, G = ') ON [PRIMARY]'
FROM sys.objects t
INNER JOIN sys.indexes i
ON t.object_id = i.object_id
CROSS APPLY(SELECT col.[name] + ', '
FROM sys.index_columns ic
INNER JOIN sys.columns col
ON ic.object_id = col.object_id
AND ic.column_id = col.column_id
WHERE ic.object_id = t.object_id
AND ic.index_id = i.index_id
ORDER by key_ordinal
for xml path ('') ) D (column_names)
WHERE i.[is_primary_key] = 0
AND substring(i.[name],1,1 ) != '_'
AND t.is_ms_shipped <> 1
AND index_id > 0
)
SELECT T, A + B + C + D + E + F + G
FROM MyCTE
ORDER BY T
上一篇文章中的一个小改动，将主字段和包含字段分开。
之前：
CREATE NONCLUSTERED INDEX [nci_tbl_tbl_Table_RouteRace_id] ON [dbo].[tbl_Table] (DeliveryTransaction_id, StorageObject_id, RouteRace_id) WITH ... ON [PRIMARY]
之后：
CREATE NONCLUSTERED INDEX [nci_tbl_tbl_Table_RouteRace_id] ON [dbo].[tbl_Table] (RouteRace_id ASC) INCLUDE (DeliveryTransaction_id, StorageObject_id) WITH ... ON [PRIMARY]
修改的sql脚本：
;WITH MyCTE
AS
(
SELECT T = t.[name]
,A = 'ALTER TABLE [' + schema_name(t.schema_id) + '].[' + t.[name]+'] ADD CONSTRAINT [' + i.[name] + '] PRIMARY KEY CLUSTERED (' + substring(column_names, 1, len(column_names)-1)
+isnull('INCLUDE('+ substring(column_names2, 1, len(column_names2)-1)+')','')
,B = ') WITH (PAD_INDEX = ' +
CASE WHEN i.[is_padded] = 0 THEN 'OFF'
WHEN i.[is_padded] = 1 THEN 'ON'
END
, C =', STATISTICS_NORECOMPUTE = OFF' + ', SORT_IN_TEMPDB = OFF' + ', IGNORE_DUP_KEY = ' +
CASE WHEN i.[IGNORE_DUP_KEY] = 0 THEN 'OFF'
WHEN i.[IGNORE_DUP_KEY] = 1 THEN 'ON'
END
, D = ', ONLINE = OFF' + ', ALLOW_ROW_LOCKS = ' +
CASE WHEN i.[ALLOW_ROW_LOCKS] = 0 THEN 'OFF'
WHEN i.[ALLOW_ROW_LOCKS] = 1 THEN 'ON'
END
, E = ', ALLOW_PAGE_LOCKS = ' +
CASE WHEN i.[ALLOW_PAGE_LOCKS] = 0 THEN 'OFF'
WHEN i.[ALLOW_PAGE_LOCKS] = 1 THEN 'ON'
END
, F = CASE WHEN i.[fill_factor] = 0 THEN ''
WHEN i.[fill_factor] > 0 THEN ', FILLFACTOR = ' + CAST(i.[fill_factor] AS varchar(3))
END
, G = ') ON [PRIMARY]'
FROM sys.objects t
INNER JOIN sys.indexes i
ON t.object_id = i.object_id
CROSS APPLY(SELECT col.[name] + iif(ic.is_descending_key=1,' DESC',' ASC') + ', '
FROM sys.index_columns ic
INNER JOIN sys.columns col
ON ic.object_id = col.object_id
AND ic.column_id = col.column_id
WHERE ic.object_id = t.object_id
AND ic.index_id = i.index_id
and ic.is_included_column = 0
ORDER by key_ordinal
for xml path ('') ) D (column_names)
CROSS APPLY(SELECT col.[name] + ', '
FROM sys.index_columns ic
INNER JOIN sys.columns col
ON ic.object_id = col.object_id
AND ic.column_id = col.column_id
WHERE ic.object_id = t.object_id
AND ic.index_id = i.index_id
and ic.is_included_column = 1
ORDER by key_ordinal
for xml path ('') ) Dinclided (column_names2)
WHERE i.[is_primary_key] = 1
AND t.is_ms_shipped <> 1
AND index_id > 0
UNION
SELECT T = t.[name]
,A = 'CREATE ' +
CASE WHEN i.[type] = 1 THEN 'CLUSTERED'
WHEN i.[type] = 2 THEN 'NONCLUSTERED'
WHEN i.[type] = 3 THEN 'XML'
WHEN i.[type] = 4 THEN 'Spatial '
WHEN i.[type] = 5 THEN 'Clustered columnstore'
WHEN i.[type] = 6 THEN 'Nonclustered columnstore'
WHEN i.[type] = 7 THEN 'Nonclustered hash'
END
, B = ' INDEX [' + i.[name] + '] ON ['+ schema_name(t.schema_id) + '].[' + t.[name]+'] ('+substring(column_names, 1, len(column_names)-1) + ')'
+isnull('INCLUDE('+ substring(column_names2, 1, len(column_names2)-1)+')','')+
+ ' WITH (PAD_INDEX = ' +
CASE WHEN i.[is_padded] = 0 THEN 'OFF'
WHEN i.[is_padded] = 1 THEN 'ON'
END
, C = ', STATISTICS_NORECOMPUTE = OFF' + ', SORT_IN_TEMPDB = OFF' + ', IGNORE_DUP_KEY = ' +
CASE WHEN i.[IGNORE_DUP_KEY] = 0 THEN 'OFF'
WHEN i.[IGNORE_DUP_KEY] = 1 THEN 'ON'
END
, D = ', ONLINE = OFF' + ', ALLOW_ROW_LOCKS = ' +
CASE WHEN i.[ALLOW_ROW_LOCKS] = 0 THEN 'OFF'
WHEN i.[ALLOW_ROW_LOCKS] = 1 THEN 'ON'
END
, E = ', ALLOW_PAGE_LOCKS = ' +
CASE WHEN i.[ALLOW_PAGE_LOCKS] = 0 THEN 'OFF'
WHEN i.[ALLOW_PAGE_LOCKS] = 1 THEN 'ON'
END
, F = CASE WHEN i.[fill_factor] = 0 THEN ''
WHEN i.[fill_factor] > 0 THEN ', FILLFACTOR = ' + CAST(i.[fill_factor] AS varchar(3))
END
, G = ') ON [PRIMARY]'
FROM sys.objects t
INNER JOIN sys.indexes i
ON t.object_id = i.object_id
CROSS APPLY(SELECT col.[name] + iif(ic.is_descending_key=1,' DESC',' ASC') + ', '
FROM sys.index_columns ic
INNER JOIN sys.columns col
ON ic.object_id = col.object_id
AND ic.column_id = col.column_id
WHERE ic.object_id = t.object_id
AND ic.index_id = i.index_id
and ic.is_included_column = 0
ORDER by key_ordinal
for xml path ('') ) D (column_names)
CROSS APPLY(SELECT col.[name] + ', '
FROM sys.index_columns ic
INNER JOIN sys.columns col
ON ic.object_id = col.object_id
AND ic.column_id = col.column_id
WHERE ic.object_id = t.object_id
AND ic.index_id = i.index_id
and ic.is_included_column = 1
ORDER by key_ordinal
for xml path ('') ) Dinclided (column_names2)
WHERE i.[is_primary_key] = 0
AND substring(i.[name],1,1 ) != '_'
AND t.is_ms_shipped <> 1
AND index_id > 0
)
SELECT T, A + B + C + D + E + F + G
FROM MyCTE
ORDER BY T