--获取索引,约束(主键)的DDL
declare @tabname varchar(50)
set @tabname='testbzm'--表名
if ( object_id('tempdb.dbo.#IDX') is not null)
begin
DROP TABLE #IDX
DROP TABLE #IDX2
DROP TABLE #IDX3
end
SELECT a.name IndexName,
c.name TableName,
d.name IndexColumn,
i.is_primary_key,--为主键=1,其他为0
i.is_unique_constraint, --唯一约束=1,其他为0
b.keyno --列的次序,0为include的列
into #IDX
FROM sysindexes a
JOIN sysindexkeys b
ON a.id = b.id
AND a.indid = b.indid
JOIN sysobjects c
ON b.id = c.id
JOIN syscolumns d
ON b.id = d.id
AND b.colid = d.colid
join sys.indexes i
on i.index_id=a.indid and c.id=i.object_id
WHERE a.indid NOT IN (0, 255) --indid = 0 或 255则为表,其他为索引。
-- and c.xtype='U' /*U = 用户表*/ and c.status>0 --查所有用户表
AND c.name = @tabname --查指定表
and c.type <> 's' --S = 系统表
ORDER BY c.name, a.name,b.keyno asc
SELECT IndexName,
TableName,
is_primary_key, --为主键=1,其他为0
is_unique_constraint, --唯一约束=1,其他为0
[IndexColumn] =
stuff (
(SELECT ',' + [IndexColumn]
FROM (select * from #IDX where keyno<>0) n
WHERE t.IndexName = n.IndexName
AND t.TableName = n.TableName
AND t.is_primary_key = n.is_primary_key
AND t.is_unique_constraint = n.is_unique_constraint
FOR XML PATH ( '' )),
1,
1,
'')
into #IDX2
FROM (select * from #IDX where keyno<>0) t
GROUP BY IndexName,
TableName,
is_primary_key,
is_unique_constraint
SELECT IndexName,
TableName,
is_primary_key, --为主键=1,其他为0
is_unique_constraint, --唯一约束=1,其他为0
[IndexColumn] =
stuff (
(SELECT ',' + [IndexColumn]
FROM (select * from #IDX where keyno=0) n
WHERE t.IndexName = n.IndexName
AND t.TableName = n.TableName
AND t.is_primary_key = n.is_primary_key
AND t.is_unique_constraint = n.is_unique_constraint
FOR XML PATH ( '' )),
1,
1,
'')
into #IDX3
FROM (select * from #IDX where keyno=0) t
GROUP BY IndexName,
TableName,
is_primary_key,
is_unique_constraint
select case
when a.is_primary_key=1 then 'ALTER TABLE '+a.tablename+' ADD CONSTRAINT '+a.indexname+' PRIMARY KEY ('+a.IndexColumn+')'
when a.is_unique_constraint=1 then 'ALTER TABLE '+a.tablename+' ADD CONSTRAINT '+a.indexname+' UNIQUE NONCLUSTERED('+a.IndexColumn+') WITH(ONLINE=ON,FillFactor=90)'
else 'create index '+a.indexname+' on '+a.tablename+'('+a.IndexColumn+') '+
(case when b.IndexColumn is null then '' else 'include('+b.IndexColumn+') ' end)+'WITH(ONLINE=ON,FillFactor=90)' end INDEX_DDL
from #IDX2 a left join #IDX3 b on a.indexname=b.indexname
where a.is_primary_key=0 --去掉主键
【SQLSERVER】获取索引,约束(主键)的DDL
最新推荐文章于 2022-03-29 13:35:38 发布