【转】SQL SERVER获取索引脚本

关于如何获取索引脚本的语句很多，上次在项目中需要去查询并获取索引脚本，所以写了一个简单的查询语句来进行获取。

 

 
--USE [WQBNEW_4.3.0]
--GO



WITH    idxcol
          AS ( SELECT
                i.object_id ,
                i.index_id ,
                OBJECT_NAME(i.object_id) AS objname ,
                i.name AS idxname ,
                ocol.name AS colname ,
                i.type AS idxtype ,
                i.type_desc AS idxtypedesc ,
                i.is_unique ,
                i.is_primary_key ,
                i.is_unique_constraint ,
                i.fill_factor ,
                icol.key_ordinal AS idxcoloder ,
                icol.is_descending_key ,
                icol.is_included_column ,
                pt.row_count ,
                pt.used_page_count * 8 *1024.0 / POWER(1024, 2) AS [usedrowpage_mb] ,
                pt.reserved_page_count * 8 *1024.0 / POWER(1024, 2) AS [allrowpage_MB]--,  
  --* 
               FROM
                sys.indexes i ,
                sys.index_columns icol ,
                sys.columns ocol ,
                sys.dm_db_partition_stats pt
               WHERE
                i.object_id = icol.object_id
                AND i.index_id = icol.index_id
                AND icol.object_id = ocol.object_id
                AND icol.column_id = ocol.column_id
                AND i.object_id = pt.object_id
                AND i.index_id = pt.index_id               
                AND EXISTS ( SELECT
                                1
                             FROM
                                sys.objects o
                             WHERE
                                o.object_id = i.object_id
    
                             AND o.type = 'U' ))

  SELECT
    * ,
    N'IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''[' + t.objname COLLATE Latin1_General_CI_AS_KS_WS + N']'') AND name = N''' + t.idxname COLLATE Latin1_General_CI_AS_KS_WS + N''') DROP INDEX [' + t.idxname COLLATE Latin1_General_CI_AS_KS_WS + N'] ON [dbo].[' + t.objname COLLATE Latin1_General_CI_AS_KS_WS + N']; ' +
    N'CREATE ' + t.typedesc COLLATE Latin1_General_CI_AS_KS_WS + 
 N' INDEX [' + t.idxname COLLATE Latin1_General_CI_AS_KS_WS + 
 N'] ON [' + t.objname COLLATE Latin1_General_CI_AS_KS_WS +
 N'](' + CASE WHEN t.colsinc IS NULL THEN 
  t.cols COLLATE Latin1_General_CI_AS_KS_WS 
 ELSE 
 --REPLACE(cols,t.colsinc,'') COLLATE Latin1_General_CI_AS_KS_WS 
 SUBSTRING(cols,LEN(colsinc)+2,LEN(cols)-LEN(colsinc)) 
 END 
 + N')'+CASE WHEN t.colsinc IS NOT NULL THEN ' INCLUDE('+t.colsinc+')' ELSE ' ' END  AS [indexsql]
  FROM
    ( SELECT 
  DISTINCT
        object_id ,
        index_id ,
        objname ,
        idxname ,
        idxtypedesc ,
        CASE WHEN is_primary_key = 1 THEN 'prmiary key'
             ELSE CASE WHEN is_unique_constraint = 1 THEN 'unique constraint'
                       ELSE CASE WHEN is_unique = 1 THEN 'Unique '
                                 ELSE ''
                            END + idxtypedesc
                  END
        END AS typedesc ,
        STUFF(( SELECT
                    ',' + colname + CASE WHEN is_descending_key = 1 THEN ' desc'
                                         ELSE ''
                                    END
                FROM
                    idxcol
                WHERE
                    object_id = c.object_id
                    AND index_id = c.index_id
                ORDER BY
                    idxcoloder
              FOR
                XML PATH('') ), 1, 1, '') AS cols ,
        STUFF(( SELECT
                    ',' + colname
                FROM
                    idxcol
                WHERE
                    object_id = c.object_id
                    AND index_id = c.index_id
                    AND is_included_column = 1
                ORDER BY
                    idxcoloder
              FOR
                XML PATH('') ), 1, 1, '') AS colsinc ,
        row_count ,
        [allrowpage_MB] ,
        [usedrowpage_mb] ,
        [allrowpage_MB] - [usedrowpage_mb] AS unusedrowpage_mb
      FROM
        idxcol c ) AS t        
        
        --where t.objname='pro_order_list' --表名
        --AND   (CHARINDEX(',OrderId,', ','+ t.cols+',') > 0  OR charIndex(',OrderId,', ','+ t.colsinc+',') > 0 )     --列名

 

 

 转自：https://blog.csdn.net/fredrickhu/article/details/62217802

--获取某表的所有索引，包括索引归属列名
sp_statistics '表名'
--获取索引与索引归属列以及描述信息
sp_helpindex '表名'
--获取某表的约束信息以及归属列
sp_helpconstraint '表名'