关闭

【SQLSERVER】获取索引,约束(主键)的DDL

标签: sql server
108人阅读 评论(0) 收藏 举报
分类:
--获取索引,约束(主键)的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 = 0255则为表,其他为索引。
      -- 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 --去掉主键
0
0

猜你在找
【直播】机器学习&数据挖掘7周实训--韦玮
【套餐】系统集成项目管理工程师顺利通关--徐朋
【直播】3小时掌握Docker最佳实战-徐西宁
【套餐】机器学习系列套餐(算法+实战)--唐宇迪
【直播】计算机视觉原理及实战--屈教授
【套餐】微信订阅号+服务号Java版 v2.0--翟东平
【直播】机器学习之矩阵--黄博士
【套餐】微信订阅号+服务号Java版 v2.0--翟东平
【直播】机器学习之凸优化--马博士
【套餐】Javascript 设计模式实战--曾亮
查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:35788次
    • 积分:1696
    • 等级:
    • 排名:千里之外
    • 原创:140篇
    • 转载:8篇
    • 译文:0篇
    • 评论:0条