作为排序规则更改练习的一部分,我有一个索引列表 (122) 需要删除然后重新创建。如何重新创建这些索引,而不必每次都通过 GUI 并将其编写脚本到查询窗口?
我的索引列表是从此脚本获取的
WITH indexCTE AS
(
SELECT Table_Name, Column_Name, Collation_Name
FROM information_schema.columns
WHERE Collation_Name IS NOT NULL AND Collation_Name = 'Modern_Spanish_CI_AS'
),
indexCTE2 AS
(
SELECT i.Name [Index Name], OBJECT_NAME(i.object_ID) [Table Name], c.Name [Column Name]
FROM sys.indexes i
INNER JOIN sys.index_columns ic ON i.index_id = ic.index_id AND i.object_id = ic.object_id
INNER JOIN sys.columns c ON ic.column_id = c.column_id AND ic.object_id = c.OBJECT_ID
WHERE EXISTS (SELECT 1 FROM indexCTE t1 WHERE t1.Table_Name = OBJECT_NAME(i.object_ID) AND t1.Column_Name = c.Name)
) SELECT * FROM indexCTE2
正如您可能知道的那样,我仍然是一名初级 DBA,所以请耐心等待!
Thanks!
我想说,你已经很接近了 - 我尝试过这个,你能验证一下这是否适合你,并向你显示预期要重新创建的 122 个索引吗?
UPDATE:添加了确定 CLUSTERED 与 NONCLUSTERED 索引类型以及将 INCLUDEd 列添加到索引定义的功能。
WITH indexCTE AS
(
SELECT DISTINCT
i.index_id, i.name, i.object_id
FROM
sys.indexes i
INNER JOIN
sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE
EXISTS (SELECT * FROM sys.columns c
WHERE c.collation_name = 'Modern_Spanish_CI_AS'
AND c.column_id = ic.column_id AND c.object_id = ic.object_id)
),
indexCTE2 AS
(
SELECT
indexCTE.name 'IndexName',
OBJECT_NAME(indexCTE.object_ID) 'TableName',
CASE indexCTE.index_id
WHEN 1 THEN 'CLUSTERED'
ELSE 'NONCLUSTERED'
END AS 'IndexType',
(SELECT DISTINCT c.name + ','
FROM
sys.columns c
INNER JOIN
sys.index_columns ic
ON c.object_id = ic.object_id AND ic.column_id = c.column_id AND ic.Is_Included_Column = 0
WHERE
indexCTE.OBJECT_ID = ic.object_id
AND indexCTE.index_id = ic.index_id
FOR XML PATH('')
) ixcols,
ISNULL(
(SELECT DISTINCT c.name + ','
FROM
sys.columns c
INNER JOIN
sys.index_columns ic
ON c.object_id = ic.object_id AND ic.column_id = c.column_id AND ic.Is_Included_Column = 1
WHERE
indexCTE.OBJECT_ID = ic.object_id
AND indexCTE.index_id = ic.index_id
FOR XML PATH('')
), '') includedcols
FROM
indexCTE
)
SELECT
'CREATE ' + IndexType + ' INDEX ' + IndexName + ' ON ' + TableName +
'(' + SUBSTRING(ixcols, 1, LEN(ixcols)-1) +
CASE LEN(includedcols)
WHEN 0 THEN ')'
ELSE ') INCLUDE (' + SUBSTRING(includedcols, 1, LEN(includedcols)-1) + ')'
END
FROM
indexCTE2
ORDER BY
TableName, IndexName
你得到了吗CREATE INDEX
您正在寻找的声明?
Marc
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)