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