sql – 为索引列表生成CREATE脚本

作为校对更改练习的一部分,我有一个索引列表(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所以请耐心等待我！

谢谢！

解决方法

更新：添加了确定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,LEN(includedcols)-1) + ')'
        END
FROM 
   indexCTE2
ORDER BY 
   TableName,IndexName

你得到你正在寻找的CREATE INDEX语句吗？

渣子

（编辑：常州站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!