SQL SERVER 2005 获取表的所有索引信息以及删除和新建语句
2023-09-14 08:58:57 时间
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 提取数字、提取英文、提取中文的sql语句
- SQL Server如何通过SQL语句直接操作另一台服务器上的SQL SERVER的数据
- SQL SERVER 分组求和sql语句
- 如何使用SQL Server导出数据(sqlserver导出数据sql)
- SQL Server自增索引:优化性能(sqlserver自增)
- SQL Server中建立索引的步骤与技巧(sqlserver建立索引)
- 提高查询效率!实现数据分离!深度解析SQL Server分区索引(sqlserver分区索引)
- SQL Server中的分区索引技巧(sqlserver分区索引)
- 索引SQL Server聚集索引——优化数据查询的利器(sqlserver聚簇)
- 索引列有利SQL Server性能优化(sqlserver索引列)
- SQL Server中索引的作用和构建(sqlserver的索引)
- SQL Server快速查找索引的方法(sqlserver查索引)
- SQL Server库中强大的索引功能(sqlserver库索引)
- SQL Server双索引:优化索引查询性能(sqlserver双索引)
- SQL Server 清理垃圾:精准删除索引(sqlserver删索引)
- SQL Server指数创建:提升数据库查询性能(sqlserver做索引)
- 的作用优化SQL Server性能的秘诀:掌握索引的重要性(sqlserver中索引)
- MySQL 中 Server 的重要性(mysql中server)