SQL SERVER 2005 获取表的所有索引信息以及删除和新建语句
2023-09-14 09:00:19 时间
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 Server之旅——第七站 为什么都说状态少的字段不能建索引
- Sql Server之旅——第四站 你必须知道的非聚集索引扫描
- SQL SERVER 重组含有特殊字符的索引时遇到“关键字 'with' 附近有语法错误.”
- SQL SERVER中关于OR会导致索引扫描或全表扫描的浅析
- SQL SERVER 2012启动失败 because upgrade step 'SSIS_hotfix_install.sql' 失败
- SQL Server基础Sql语句复习
- SQL查询优化:详解SQL Server非聚集索引(转载)
- SQL SERVER 2005 获取表的所有索引信息以及删除和新建语句
- 删除指定表的所有索引,包括主键索引,唯一索引和普通索引 ,适用于sql server 2005 .
- 《转》SQL Server 2008 数据维护实务
- SQL Server replication requires the actual server name to make a connection to the server.错误解决
- 删除指定表的所有索引,包括主键索引,唯一索引和普通索引 ,适用于sql server 2005,
- 浅析SQL Server 2005中的主动式通知机制
- SQL SERVER服务器链接连接(即sql server的跨库连接)
- ROS routerOS中修改dhcp 默认dns server IP 域控服务器IP设置为dhcp 默认dns IP
- SQL Server未找到或无法訪问server问题解决
- JSP(Java Server Pages,即:Java服务器页面