SQL SERVER 2005 擷取表的所有索引資訊以及刪除和建立語句

標籤：des   blog   http   ar   os   sp   for   div   on   

原文:SQL SERVER 2005 擷取表的所有索引資訊以及刪除和建立語句

BEGIN        WITH tx AS        (                SELECT a.object_id                      ,b.name AS schema_name                      ,a.name AS table_name                      ,c.name as ix_name                      ,c.is_unique AS ix_unique                      ,c.type_desc AS ix_type_desc                      ,d.index_column_id                      ,d.is_included_column                      ,e.name AS column_name                      ,f.name AS fg_name                      ,d.is_descending_key AS is_descending_key                      ,c.is_primary_key                      ,c.is_unique_constraint                  FROM sys.tables AS a                 INNER JOIN sys.schemas AS b            ON a.schema_id = b.schema_id AND a.is_ms_shipped = 0                 INNER JOIN sys.indexes AS c            ON a.object_id = c.object_id                 INNER JOIN sys.index_columns AS d      ON d.object_id = c.object_id AND d.index_id = c.index_id                 INNER JOIN sys.columns AS e            ON e.object_id = d.object_id AND e.column_id = d.column_id                 INNER JOIN sys.data_spaces AS f        ON f.data_space_id = c.data_space_id        )        SELECT               Drop_Index   = CASE WHEN (a.is_primary_key = 1 OR a.is_unique_constraint = 1)                                   THEN ‘ALTER TABLE ‘ + a.table_name + ‘ DROP CONSTRAINT ‘ + a.ix_name                                   ELSE ‘DROP INDEX ‘ + a.ix_name collate SQL_Latin1_General_CP1_CI_AS + ‘ ON ‘ + a.schema_name + ‘.‘ + a.table_name  END              ,Create_Index = CASE WHEN (a.is_primary_key = 1 OR a.is_unique_constraint = 1)                                   THEN ‘ALTER TABLE ‘ + a.table_name + ‘ ADD CONSTRAINT ‘ + a.ix_name                                        + CASE WHEN a.is_primary_key = 1 THEN ‘ PRIMARY KEY‘ ELSE ‘ UNIQUE‘ END + ‘(‘ + indexColumns.ix_index_column_name + ‘)‘                                   ELSE ‘CREATE ‘ + CASE WHEN a.ix_unique = 1 THEN ‘UNIQUE ‘ ELSE ‘‘ END                                        + a.ix_type_desc + ‘ INDEX ‘ + a.ix_name collate SQL_Latin1_General_CP1_CI_AS + ‘ ON ‘ + a.schema_name                                       + ‘.‘ + a.table_name + ‘(‘ + indexColumns.ix_index_column_name + ‘)‘                                       + CASE WHEN IncludeIndex.ix_included_column_name IS NOT NULL THEN ‘ INCLUDE (‘ + IncludeIndex.ix_included_column_name + ‘)‘ ELSE ‘‘ END                                       + ‘ ON [‘ + a.fg_name +‘]‘ END              ,CASE WHEN a.ix_unique = 1 THEN ‘UNIQUE‘ END AS ix_unique              ,a.ix_type_desc              ,a.ix_name              ,a.schema_name              ,a.table_name              ,indexColumns.ix_index_column_name              ,IncludeIndex.ix_included_column_name              ,a.fg_name              ,a.is_primary_key              ,a.is_unique_constraint                                               FROM        (                SELECT DISTINCT                       ix_unique                      ,ix_type_desc                      ,ix_name                      ,schema_name                      ,table_name                      ,fg_name                      ,is_primary_key                      ,is_unique_constraint                  FROM tx        ) AS a        OUTER APPLY        (                SELECT ix_index_column_name                       = STUFF((                                SELECT ‘,‘ + column_name + CASE WHEN is_descending_key = 1 THEN ‘ DESC‘ ELSE ‘‘ END                                  FROM tx AS b                                 WHERE schema_name = a.schema_name                                   AND table_name=a.table_name                                   AND ix_name=a.ix_name                                   AND ix_type_desc=a.ix_type_desc                                   AND fg_name=a.fg_name                                   AND is_included_column=0                                 ORDER BY index_column_id                                   FOR XML PATH(‘‘)                                ),1,1,‘‘)        )IndexColumns        OUTER APPLY        (                SELECT ix_included_column_name                       = STUFF((                                SELECT ‘,‘ + column_name                                  FROM tx AS b                                 WHERE schema_name = a.schema_name                                   AND table_name=a.table_name                                   AND ix_name=a.ix_name                                   AND ix_type_desc=a.ix_type_desc                                   AND fg_name=a.fg_name                                   AND is_included_column=1                                 ORDER BY index_column_id                                   FOR XML PATH(‘‘)                                ), 1,1,‘‘)        )IncludeIndex        ORDER BY a.schema_name,a.table_name,a.ix_name;END

 

SQL SERVER 2005 擷取表的所有索引資訊以及刪除和建立語句