查出所有索引名称并压缩索引的TSQL
create table #tempindex(
tableName nvarchar(776),
index_name sysname,
index_description varchar(2000),
index_keys varchar(2000))
--向该临时表中插入当前库中的所有用户表的索引信息
exec sp_msforeachtable 'insert into #tempindex(index_name,index_description,index_keys)
exec sp_helpindex "?";
update #tempindex set tableName="?" where tableName is null'
--查看所有表的索引信息
Ｓelect tableName,index_name,index_keys,index_description from #tempindex
--where index_description not like 'clustered%' --非聚集索引
order by tableName
Ｓelect * into #temp from #tempindex
Ｓelect top 30 * into #ttt from #temp
declare @ind_name varchar(100),@tname varchar(100),@sql varchar(300)
while (Ｓelect count(*) from #ttt)>0
begin
Ｓelect top 1 @ind_name=index_name,@tname=tablename from #ttt
Ｓelect @sql='ALTER INDEX '+@ind_name+' ON '+@tname+' REBUILD WITH (DATA_COMPRESSION = page)'
print (@sql)
exec (@sql)
delete from #ttt where index_name=@ind_name and tablename=@tname
delete from #temp where index_name=@ind_name and tablename=@tname
end
drop table #ttt
drop table #tempindex
drop table #temp