Technical Article

Defragment all table indecies

,

To help maintain index quality, defragging of any given index is a good thing. This will defrag ALL indecies in the current DB. NOTE: Schedule for off-hours operations.

For a massive re-write of indecies, change the remarking on "--IF @indid" lines to use reindex instead of just defragging.

/*
Defrag or re-index all indecies
*/
DECLARE @TableName sysname
DECLARE @IndexName VARCHAR(100)
DECLARE @indid int
DECLARE cur_tblfetch CURSOR FOR
SELECT name from sysobjects where xtype='U'

OPEN cur_tblfetch
FETCH NEXT FROM cur_tblfetch INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN 
DECLARE cur_indfetch CURSOR FOR
SELECT indid,name FROM sysindexes WHERE id = OBJECT_ID (@TableName) and keycnt > 0 
OPEN cur_indfetch
FETCH NEXT FROM cur_indfetch INTO @indid,@IndexName
WHILE @@FETCH_STATUS = 0
BEGIN 
  print 'Maintenancing ' + (@TableName) + '.' + (@IndexName)
  --IF @indid <> 255 DBCC INDEXDEFRAG (0, @TableName, @indid)
  IF @indid <> 255 DBCC DBREINDEX (@TableName, @IndexName)
  FETCH NEXT FROM cur_indfetch INTO @indid,@IndexName
END
CLOSE cur_indfetch
DEALLOCATE cur_indfetch
FETCH NEXT FROM cur_tblfetch INTO @TableName
END
CLOSE cur_tblfetch
DEALLOCATE cur_tblfetch

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating