SQL SERVER获取索引脚本
来源:互联网 发布:2014淘宝全年销售额 编辑:程序博客网 时间:2024/06/06 01:43
关于如何获取索引脚本的语句很多,上次在项目中需要去查询并获取索引脚本,所以写了一个简单的查询语句来进行获取。
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获取索引脚本
- sql server生成索引创建脚本
- SQL Server 2005:索引碎片整理脚本
- SQL Server 获取表的所有索引
- 获取SQL Server 数据字典脚本
- 获取SQL Server CPU使用数据的脚本
- 获取某月第一天,最后一天的sql server脚本
- 获取某月第一天,最后一天的sql server脚本
- SQL server 索引
- 优化SQL Server索引
- 优化SQL Server索引
- SQL Server 索引碎片
- sql Server 索引优化
- SQL Server全文索引
- sql Server索引优化
- sql Server 索引优化
- SQL Server创建索引
- SQL Server 索引维护
- 细数阿里云服务器的十二种典型应用场景
- ios NSArray NSMutableArray NSDictionary NSMutableDictionary使用
- 批量打印二维码的软件---使用心得(领跑条码标签打印软件)
- C++文件操作
- mac上使用cocos studio 经常随意奔溃问题解决方法
- SQL SERVER获取索引脚本
- JSON的简单书写(初学),使用Java语言反序列化
- 函数参数默认值设置
- 理解JAVASCRIPT 中hasOwnProperty()的作用
- 阿里云工程师用机器学习破解雾霾成因
- MyBatis学习四 懒加载和缓存
- L2-011. 玩转二叉树
- 数据结构之二叉排序树
- MaxCompute(原ODPS) MapReduce常见问题解答