生成指定表rebuild所有索引的语句代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】生成指定表rebuild所有索引的语句

作者:yangzhawen / 发布于2012/2/21/ 698

需要对表大数据量操作的时候，如delete，需要对索引可以选择性的操作！可以使用下面语句生成:

<div>declare @tname varchar(100)
declare @size int
set @size=0     --这里设置索引大小限制，如果不设置默认为0即所有索引 
set @tname='tblorders'
select  'alter index '+' '+c.indexname+' '+'on'+' <a href="mailto:'+@tname+'">'+@tname+'</a> '+'rebuild with (data_compression=page)'
from 
(
select * from 
(
SELECT
OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
i.index_id AS IndexID,
8 * SUM(a.used_pages)/1024 AS 'Indexsize(MB)'
FROM sys.indexes AS i
JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
GROUP BY i.OBJECT_ID,i.index_id,i.name
)a
where <a href="mailto:a.tablename=@tname">a.tablename=@tname</a> and
[Indexsize(MB)]>@size  
)
</div>

试试其它关键字

　rebuild所有索引　

同语言下

可能有用的

yangzhawen贡献的其它代码(5)