sqlserver 重新生成索引

根子 index sql 2023-05-16 10:59 原创 834

闲暇无事写了个存储过程用于重新所有生成索引,原理很简单就是先获取待处理的数量,然后循环执行alter index 语句,重点是要能获取到索引名称和表名。

定期重新生成索引,可以大大缩减磁盘空间,清理碎片,建议使用sql作业。

declare @total int =(select COUNT(0) from (select distinct s.name,so.name TableName from sysindexes s join sysindexkeys si on s.id=si.id and s.indid=si.indid join sysobjects so on so.id=si.id  where so.type='U' ) t )
declare @i int=0
declare @idxid int=0
declare @idxname nvarchar(512)
declare @idxtabname nvarchar(512)
while(@i<@total)
begin
set @i=@i+1;
--print @i
select distinct top 1 @idxname=s.name,@idxtabname=so.name from sysindexes s join sysindexkeys si on s.id=si.id and s.indid=si.indid join sysobjects so on so.id=si.id  where so.type='U' 
and s.name not in(select distinct top (@i-1) s.name from sysindexes s join sysindexkeys si on s.id=si.id and s.indid=si.indid join sysobjects so on so.id=si.id  where so.type='U' order by s.name) order by s.name;
exec('ALTER INDEX '+ @idxname +' ON '+@idxtabname+' REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ');
print @idxtabname+' '+@idxname+' '+ Convert(nvarchar(128),getdate())+' done.' 
end
print 'all done.'

如果你要Copy请注明来源,谢谢!

留 言

木艮子の笔记