闲暇无事写了个存储过程用于重新所有生成索引,原理很简单就是先获取待处理的数量,然后循环执行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请注明来源,谢谢!