关于如何获取索引脚本的语句很多，上次在项目中需要去查询并获取索引脚本，所以写了一个简单的查询语句来进行获取。
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
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
