注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

Oracle专业打杂

定会重回巅峰……

 
 
 

日志

 
 

【转】 SQL SERVER 2005 获取表的所有索引信息以及删除和新建语句  

2016-02-18 14:34:55|  分类: MSSQL基础知识 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
BEGIN
        
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
        )
        
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' END AS ix_unique
              ,a.ix_type_desc
              ,a.ix_name
              ,a.schema_name
              ,a.table_name
              ,indexColumns.ix_index_column_name
              ,IncludeIndex.ix_included_column_name
              ,a.fg_name
              ,a.is_primary_key
              ,a.is_unique_constraint                                       
        
FROM
        (
                
SELECT DISTINCT
                       ix_unique
                      ,ix_type_desc
                      ,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;
END

(原文地址:http://blog.itpub.net/16436858/viewspace-609341/)
  评论这张
 
阅读(90)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017