ms sql server 2005版本以上查询数据库索引信息

ms sql  server 2005版本以上查询数据库索引信息，并生成创建索引，删除索引的语句，对DBA管理索引很有帮助

ms sql  server 2005数据库索引有include功能，对查询优化很有用，通过此代码可以生成创建索引语句中可以生成包含 include子句的索引，在代码中可以指定表名

 

;with TB
as
(
	Select 
		TB.object_id,
		Schema_name = Sch.name,
		table_name = TB.name
	From sys.tables TB
		Inner join sys.schemas Sch
			on TB.schema_id = Sch.schema_id
	where TB.is_ms_shipped = 0	
),
IXC AS
(
	SELECT
		IXC.object_id,IXC.index_id,IXC.index_column_id,
		IXC.is_descending_key,IXC.is_included_column,
		column_name = C.name
	FROM SYS.index_columns IXC
		INNER JOIN SYS.columns C
		ON IXC.object_id = C.object_id
		AND IXC.column_id = C.column_id
)
,
IX as
(
	Select
		IX.object_id,
		index_name = IX.name,
		index_type_desc = IX.type_desc,
		IX.is_unique,IX.is_primary_key,IX.is_unique_constraint,
		IX.is_disabled,
		index_columns_TEMP = STUFF(IXC_COL.index_columns,1,1,N''),
		index_columns = Case when IXC_COL_INCLUDE.index_columns_includes IS NOT NULL THEN STUFF(LEFT(IXC_COL.index_columns,LEN(IXC_COL.index_columns)-LEN(IXC_COL_INCLUDE.index_columns_includes)),1,1,N'') ELSE STUFF(IXC_COL.index_columns,1,1,N'') END,
		index_columns_includes = STUFF(IXC_COL_INCLUDE.index_columns_includes,1,1,N'')	
	From sys.indexes IX
		CROSS APPLY(
			SELECT index_columns = (
				SELECT 
					N','+QUOTENAME(column_name)
				FROM IXC
				WHERE object_id = IX.object_id
					AND index_id = IX.index_id
				ORDER BY index_column_id
				FOR XML PATH(''),ROOT('r'),TYPE						
			).value('/r[1]','nvarchar(max)')
		) IXC_COL
		OUTER APPLY(
			SELECT
				index_columns_includes = (
					SELECT 
						N','+QUOTENAME(column_name)
					FROM IXC
					WHERE object_id = IX.object_id
						AND index_id = IX.index_id
						AND is_included_column = 1
					ORDER BY index_column_id
					FOR XML PATH(''),ROOT('r'),TYPE						
			).value('/r[1]','nvarchar(max)')		
				) IXC_COL_INCLUDE
		WHERE index_id >0	
)
SELECT 
	IX.index_name,
	N'Create index '+IX.index_name+N' on '+TB.table_name+N' ('+IX.index_columns+N')'+
	Case when IX.index_columns_includes is not null then Char(13)+N'INCLUDE ('+IX.index_columns_includes+N')'
	Else N'' End AS N'创建索引',
	N'Drop index '+TB.table_name+N'.'+IX.index_name,
	TB.Schema_name,TB.table_name,IX.index_name,IX.index_type_desc,
	IX.is_unique,IX.is_primary_key,IX.is_unique_constraint,
	IX.is_disabled,
	IX.index_columns,
	IX.index_columns_includes
FROM TB
	INNER JOIN IX
		ON TB.object_id = IX.object_id
ORDER BY Schema_name,table_name,IX.index_name