生成指定表rebuild所有索引的语句 - MSSQL教程

declare @tname varchar(100)

declare @size int

set @size=0     --这 里设置索引大小限制，如果不设置默认为0即所有索引

set @tname='tblorders'

select  'alter index '+' '+c.indexname+' '+'on'+' '+@tname+' '+'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.tablename=@tname and

[Indexsize(MB)]>@size  

)

c

查看本栏目更多精彩内容：http://www.bianceng.cn/database/SQLServer/

（编辑：应用网_镇江站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!