获取sqlserver指定表所有的索引构建语句DDL

低级甩锅工程师

于 2024-04-10 18:35:35 发布

阅读量125

点赞数 2

文章标签： sql 数据库 sqlserver

本文链接：https://blog.csdn.net/liangzaipeng/article/details/137606478

版权

获取sqlserver指定表所有的索引构建语句DDL
根据需要，可修改schema和table name
注释的条件可以过滤主键索引


WITH indexInfo as (
	SELECT SCHEMA_NAME(t.schema_id)  collate Japanese_CI_AS   [schema_name]  ,
	t.schema_id as schema_number,
	t.name   as [table_name],
	t1.name  as [index_name],
	t1.type  as type,
	t1.type_desc,
	t1.is_unique,
	t1.is_primary_key,
	t1.is_unique_constraint,
	t1.has_filter,
	t1.filter_definition
	,STUFF((SELECT ','+t4.name FROM sys.sysindexkeys t2 
		inner join sys.index_columns  t3 ON t2.id=t3.object_id and t2.indid=t3.index_id and t2.colid=t3.column_id
		inner join sys.syscolumns t4 ON t2.id=t4.id and t2.colid=t4.colid
		WHERE t2.id=t1.object_id and t1.index_id=t2.indid  and t2.keyno <> 0 ORDER BY t3.key_ordinal FOR XML PATH('')),1,1,'') 
		AS index_cols
	,STUFF((SELECT ','+t4.name FROM sys.sysindexkeys t2 
		inner join sys.index_columns  t3 ON t2.id=t3.object_id and t2.indid=t3.index_id and t2.colid=t3.column_id
		inner join sys.syscolumns t4 ON t2.id=t4.id and t2.colid=t4.colid
		WHERE t2.id=t1.object_id and t1.index_id=t2.indid  and t2.keyno = 0 ORDER BY t3.key_ordinal FOR XML PATH('')),1,1,'')  
		AS include_cols
	FROM sys.tables as t
	inner join sys.indexes as t1 on (t1.index_id > 0 and t1.is_hypothetical = 0) and (t1.object_id=t.object_id)
	WHERE t1.type in(1,2) and schema_name(t.[schema_id])='dbo' -- 查看schema 对应的id
	and t.name in(
			
			'xxxx'
	)
), indexInfo2 AS (
SELECT * ,(CASE 
	WHEN is_primary_key = 1 
		THEN 'alter table '+[schema_name]+'.'+[table_name]+' add constraint '+[index_name]+' primary key '+(CASE WHEN [type]=1 THEN 'clustered' ELSE 'nonclustered' END)+'('+index_cols+')'
	WHEN is_unique = 1 AND is_unique_constraint = 1 
		THEN 'alter table '+[schema_name]+'.'+[table_name]+' add constraint '+[index_name]+' unique '+(CASE WHEN [type]=1 THEN 'clustered' ELSE 'nonclustered' END)+'('+index_cols+')'
	WHEN is_unique = 1 AND (is_primary_key = 0 OR is_unique_constraint = 0)
		THEN 'create unique '+(CASE WHEN [type]=1 THEN 'clustered' ELSE 'nonclustered' END)+' index '+[index_name]+'  on '+[schema_name]+'.'+[table_name]+'('+index_cols+')'
	ELSE 'create '+(CASE WHEN [type]=1 THEN 'clustered' ELSE 'nonclustered' END)+' index '+[index_name]+' on '+[schema_name]+'.'+[table_name]+'('+index_cols+') '
	END) script
FROM indexInfo
) SELECT [schema_name],[schema_number],[table_name],[index_name],script
+(CASE WHEN include_cols IS NOT NULL THEN ' include('+include_cols+')' ELSE '' END)
+(CASE WHEN has_filter = 1 THEN ' where '+filter_definition ELSE '' END) 
+';', is_primary_key
FROM indexInfo2
--where is_primary_key = 0 
ORDER BY [schema_name],[table_name],[type],[index_name],is_primary_key DESC,is_unique_constraint DESC,is_unique DESC
GO