需要對表大數據量操作的時候，如delete，需要對索引可以選擇性的操作！可以使用下面語句生 成:
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
查看本欄目
