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
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。