生成表索引的创建和删除语句

--生成表索引的创建删除语句

;
WITH    TB
          AS ( SELECT   TB.object_id ,
                        Schema_name = Sch.name ,
                        table_name = TB.name
               FROM     sys.tables TB
                        INNER JOIN sys.schemas Sch ON TB.schema_id = Sch.schema_id
               WHERE    TB.is_ms_shipped = 0
             ),
        IXC
          AS ( SELECT   IXC.object_id ,
                        IXC.index_id ,
                        IXC.index_column_id ,
                        IXC.is_descending_key ,
                        IXC.is_included_column ,
                        column_name = C.name
               FROM     SYS.index_columns IXC
                        INNER JOIN SYS.columns C ON IXC.object_id = C.object_id
                                                    AND IXC.column_id = C.column_id
             ),
        IX
          AS ( SELECT   IX.object_id ,
                        index_name = IX.name ,
                        index_type_desc = IX.type_desc ,
                        IX.is_unique ,
                        IX.is_primary_key ,
                        IX.is_unique_constraint ,
                        IX.is_disabled ,
                        index_columns_TEMP = STUFF(IXC_COL.index_columns, 1, 1,
                                                   N'') ,
                        index_columns = CASE WHEN IXC_COL_INCLUDE.index_columns_includes IS NOT NULL
                                             THEN STUFF(LEFT(IXC_COL.index_columns,
                                                             DATALENGTH(IXC_COL.index_columns)
                                                             - DATALENGTH(IXC_COL_INCLUDE.index_columns_includes)),
                                                        1, 1, N'')
                                             ELSE STUFF(IXC_COL.index_columns,
                                                        1, 1, N'')
                                        END ,
                        index_columns_includes = STUFF(IXC_COL_INCLUDE.index_columns_includes,
                                                       1, 1, N'')
               FROM     sys.indexes IX
                        CROSS APPLY ( SELECT    index_columns = ( SELECT
                                                              N','
                                                              + QUOTENAME(column_name)
                                                              FROM
                                                              IXC
                                                              WHERE
                                                              object_id = IX.object_id
                                                              AND index_id = IX.index_id
                                                              ORDER BY index_column_id
                                      FOR       XML PATH('') ,
                                                    ROOT('r') ,
                                                    TYPE      
   ).value('/r[1]', 'nvarchar(max)')
                                    ) IXC_COL
                        OUTER APPLY ( SELECT    index_columns_includes = ( SELECT
                                                              N','
                                                              + QUOTENAME(column_name)
                                                              FROM
                                                              IXC
                                                              WHERE
                                                              object_id = IX.object_id
                                                              AND index_id = IX.index_id
                                                              AND is_included_column = 1
                                                              ORDER BY index_column_id
                                      FOR       XML PATH('') ,
                                                    ROOT('r') ,
                                                    TYPE      
   ).value('/r[1]', 'nvarchar(max)')
                                    ) IXC_COL_INCLUDE
               WHERE    index_id > 0
             )
    SELECT  DB_NAME() AS N'数据库名' ,
            TB.Schema_name AS N'架构' ,
            TB.table_name AS N'表名' ,
            IX.index_name AS N'索引名' ,
            IX.index_type_desc AS N'索引类型' ,
            IX.is_unique AS N'是否唯一索引' ,
            IX.is_primary_key AS N'是否主键' ,
            IX.is_unique_constraint AS N'是否唯一约束' ,
            IX.is_disabled AS N'是否禁用索引' ,
            IX.index_columns AS N'索引列' ,
            IX.index_columns_includes AS N'索引包含列' ,
            N'CREATE INDEX ' + N'[' + IX.index_name + N']' + N' ON ' + N'['
            + QUOTENAME(DB_NAME()) + N'.' + QUOTENAME(TB.SCHEMA_NAME) + N'.'
            + QUOTENAME(TB.table_name) + N'(' + IX.index_columns + N')'
            + CASE WHEN IX.index_columns_includes IS NOT NULL
                   THEN CHAR(13) + N'INCLUDE (' + IX.index_columns_includes
                        + N')'
                   ELSE N''
              END AS N'创建索引' ,
            N'DROP INDEX ' + QUOTENAME(IX.index_name) + N' ON  '
            + QUOTENAME(DB_NAME()) + N'.' + QUOTENAME(TB.SCHEMA_NAME) + N'.'
            + QUOTENAME(TB.table_name) AS N'删除索引'
    FROM    TB
            INNER JOIN IX ON TB.object_id = IX.object_id
    --and IX.index_type_desc<>'CLUSTERED'
    ORDER BY Schema_name ,
             table_name ,
             IX.index_name