用sql 来生成索引

41 篇文章 2 订阅 ¥19.90 ¥99.00
本文提供了一段SQL代码,用于查看和操作数据库中的索引。包括了如何根据索引的详细信息生成删除和创建索引的语句,涵盖了主键、唯一约束和普通索引。适用于数据库维护和优化。
摘要由CSDN通过智能技术生成
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
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值