【转】SQL SERVER获取索引脚本

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了【转】SQL SERVER获取索引脚本，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3915字，纯文字阅读大概需要6分钟。

内容图文

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

 

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‘CREATE ‘ + t.idxtypedesc 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 
  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

【转】SQL SERVER获取索引脚本

标签：jna   path   esc   for   fill   substring   factor   sts   _id   

本文系统来源：http://www.cnblogs.com/059212315/p/7198560.html

内容总结

以上是互联网集市为您收集整理的【转】SQL SERVER获取索引脚本全部内容，希望文章能够帮你解决【转】SQL SERVER获取索引脚本所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---