获取数据库中所有的索引信息
类别:数据库 浏览量:475
时间:2016-2-18 获取数据库中所有的索引信息
获取数据库中所有的索引信息SQL SERVER中数据库中所有的索引信息的方法
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
where a.name<>'sysdiagrams'
)
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' else '' END AS IXUnique
,case when a.ix_type_desc='CLUSTERED' then '聚集索引' when a.ix_type_desc='NONCLUSTERED' then '非聚集索引' else '' end as IXtype
,a.ix_name as IXName
,a.object_id as TableId
,a.table_name as TableName
,indexColumns.ix_index_column_name as ColumnsName
,IncludeIndex.ix_included_column_name as IncludeColumnsName
,a.fg_name
,a.is_primary_key
,a.is_unique_constraint
FROM
(
SELECT DISTINCT
ix_unique
,ix_type_desc
,object_id
,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
例如
标签:索引
您可能感兴趣
- elasticsearch 索引创建过程(使用elasticsearch定时删除索引数据)
- 数据库创建索引的注意事项
- python代码如何进行切片索引(Python读取Pickle文件信息并计算与当前时间间隔的方法分析)
- mysql未使用索引的查询(如何在mysql进行查询缓存及失败的解决方法)
- sqlserver非唯一索引汇总性能(详解SQL Server的聚焦过滤索引)
- mysql 索引使用总结(MySQL复合索引的深入探究)
- sql server重建索引
- mysql 索引怎么实现(Mysql中索引和约束的示例语句)
- SqlServer索引的原理与应用详解(SqlServer索引的原理与应用详解)
- linux更改inode(详解Linux索引节点inode)
- mysql 索引表空间(MySQL如何构建数据表索引)
- mysql索引失效怎么办(MySQL选错索引的原因以及解决方案)
- mysql索引提高效率(MySql如何查看索引并实现优化)
- mysql索引b+树和b树(MySQL使用B+Tree当索引的优势有哪些)
- mysql索引详解及基本用法(Mysql普通索引与唯一索引的选择详析)
- mysql快速创建索引(MySQL创建高性能索引的全步骤)
- 省 市书法家协会 送万福进万家 活动走进禹州美丽乡村(省市书法家协会)
- 点赞 禹州苌庄正式撤乡建镇 未来发展不可估量(禹州苌庄正式撤乡建镇)
- 它荣获 中国生态魅力镇 称号 就在咱们禹州,一起来看看(中国生态魅力镇)
- 真牛 禹州将建成中等城市(禹州将建成中等城市)
- 被骂欺师灭祖,与郭德纲公开叫板,何云伟改名何沄伟,开始画画了(与郭德纲公开叫板)
- 相声转行影帝,被何晴抛弃,甩10年女友闪婚生子,刘威不靠谱情史(相声转行影帝被何晴抛弃)
热门推荐
- 程序员哪些情况可以考虑辞职
- dedecms关闭站点(dedecms搬家后出现/include/templets/default/index.htm Not Found!解决方案)
- 详解Vue3中Teleport的使用(详解Vue3中Teleport的使用)
- 属于web服务器的有哪些(web服务器有几种类型?)
- sqlserver存储删除过程(Sql中存储过程的定义、修改和删除操作)
- mysql实用教程(Mysql调优Explain工具详解及实战演练推荐)
- mysql详细学习笔记(Mysql常用命令 详细整理版)
- table rowspan和colspan
- python简单代码实例(Python实现 版本号对比功能的实例代码)
- mysql模糊查询语句(mysql中like % %模糊查询的实现)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9