获取数据库中所有的索引信息
类别:数据库 浏览量: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
例如
标签:索引
您可能感兴趣
- Asp.Net中索引器的用法
- mysql怎么创建联合索引(mysql的联合索引复合索引的实现)
- mysql 索引怎么实现(Mysql中索引和约束的示例语句)
- sqlserver查看创建的索引(浅述SQL Server的聚焦强制索引查询条件和Columnstore Index)
- mysql快速创建索引(MySQL创建高性能索引的全步骤)
- 聚集索引和主键的区别
- flask快速开发框架(手把手教你使用Flask搭建ES搜索引擎预备篇)
- mysql索引面试总结(Mysql数据库索引面试题程序员基础技能)
- mysql索引为什么是b+树(mysql 使用B+树索引有哪些优势)
- pandas如何删除索引列(pandas DataFrame 删除重复的行的实现方法)
- SQL Server多列索引
- sqlserver中复合索引(浅析SQL Server 聚焦索引对非聚集索引的影响)
- dedecms标签怎么用(浅析DedeCMS GBK版安装sphinx全文索引无法查询无结果的解决方法)
- python 取出时间段日志(python 实现提取某个索引中某个时间段的数据方法)
- 深入理解mysql索引(MySQL中冗余和重复索引的区别说明)
- powerdesigner 如何加索引
- 四月新番CP人气榜公布,《剃须》两度上榜,沙优不是女朋友(四月新番CP人气榜公布)
- 2019年外媒秋季新番动画角色CP排行榜,桐人和爱丽丝落榜(2019年外媒秋季新番动画角色CP排行榜)
- 新一小兰领衔 盘点动漫中的那些 远距离恋爱情侣(盘点动漫中的那些)
- 大事件 合肥四中火了(大事件合肥四中火了)
- 翼龙贷组织出借人调研 感受鄱阳 借 来的致富路(翼龙贷组织出借人调研)
- 2023新国风戏曲教育寒假集训班汇报演出《戏娃闹元宵》图文报道(2023新国风戏曲教育寒假集训班汇报演出戏娃闹元宵图文报道)
热门推荐
- django对话框(基于django channel实现websocket的聊天室的方法示例)
- python爬虫出租屋(python爬虫租房信息在地图上显示的方法)
- sqlservercount函数怎么用(Sql Server中Substring函数的用法实例解析)
- System.Action委托
- mysql join规则(浅谈为什么MySQL不推荐使用子查询和join)
- php如何设置命名空间(PHP进阶学习之命名空间基本用法分析)
- 小程序可滑动弧形进度条(小程序实现文字循环滚动动画)
- linux时间同步ubuntu(在Ubuntu中使用NTP实现时间同步)
- python 配置文件环境变量(python环境路径配置以及命令行运行脚本)
- laravel开发api实例(Laravel框架集成UEditor编辑器的方法图文与实例详解)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9