SQL SERVER 2005 获取表的所有索引信息以及删除和新建语句
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 | 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 |
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步