Dynamic DBCC DBREINDEX

,

This script uses sysobjects and sysindexes to ID all tables in a database that are indexed, and/or clustered.  It then runs through per table all of the indexes and runs DBREINDEX with their fillfactor.  This eliminates having to maintain a list of all indexes and having code fail when it tries to modify indexes that no longer exist.  It also ensures that ALL indexes are processed for the database.

CREATE PROCEDURE dbo.spDBREINDEX

/*
  Created:	12/18/2002
  Created By:	AJ Ahrens - SQL DBA AT&T Labs x3375
  Purpose(s):	Dynamically reindex table(s)
*/

AS

DECLARE @TableName	VARCHAR(255)
DECLARE @IndexName	VARCHAR(255)
DECLARE @OrigFillFactor	INTEGER
DECLARE @OuterCounter	INTEGER
DECLARE @InnerCounter	INTEGER
DECLARE @OuterLoop	INTEGER
DECLARE @InnerLoop	INTEGER
DECLARE @ClusteredYN	INTEGER
DECLARE @strSQL		NVARCHAR(2000)

SELECT SO.[name] tblName, MAX(SI.OrigFillFactor) OrigFillFactor, 
  '01/01/1900 12:00:00AM' ReIndexedDtTm, SI.[name] IndexName, 
  CASE WHEN SI.indid = 1 THEN 1 ELSE 0 END ClusteredYN
INTO #IndexedTables
FROM sysindexes SI
  LEFT JOIN sysobjects SO ON SI.[id] = SO.[id]
WHERE xtype = 'U' AND SI.indid <> 0 AND SI.indid <> 255
GROUP BY SO.[name], SI.[name], SI.indid

SET @OuterCounter = 1

SET @OuterLoop = (SELECT COUNT(DISTINCT tblName) FROM #IndexedTables)

  WHILE @OuterCounter <= @OuterLoop
    BEGIN
      SET @TableName = (SELECT TOP 1 tblName FROM #IndexedTables WHERE ReIndexedDtTm = '01/01/1900 12:00:00AM')
	SET @InnerCounter = 1
	SET @InnerLoop = (SELECT COUNT(*) FROM #IndexedTables WHERE tblName = @TableName)

	WHILE @InnerCounter <= @InnerLoop
	  BEGIN
	    SET @IndexName = (SELECT TOP 1 IndexName FROM #IndexedTables WHERE ClusteredYN = 1 
				AND tblName = @TableName AND ReIndexedDtTm = '01/01/1900 12:00:00AM')
	    SET @ClusteredYN = 1

	    IF RTRIM(@IndexName) IS NULL
	      BEGIN
		SET @IndexName = (SELECT TOP 1 IndexName FROM #IndexedTables WHERE ClusteredYN = 0
				AND tblName = @TableName AND ReIndexedDtTm = '01/01/1900 12:00:00AM')
		SET @ClusteredYN = 0
	      END

	    SET @OrigFillFactor = (SELECT OrigFillFactor FROM #IndexedTables 
				WHERE tblName = @TableName AND ClusteredYN = @ClusteredYN
					AND IndexName = @IndexName)

	    SET @strSQL = 'DBCC DBREINDEX ' + '(' + CHAR(39) + @TableName + CHAR(39)
		+ CHAR(44) + SPACE(1) + CHAR(39) + @IndexName + CHAR(39) + CHAR(44) 
		+ SPACE(1) + CONVERT(VARCHAR(3), RTRIM(@OrigFillFactor)) + ')'

	    PRINT @strSQL
	    EXEC sp_executesql @strSQL

	    UPDATE #IndexedTables SET ReIndexedDtTm = GETDATE() WHERE tblName = @TableName
		AND ClusteredYN = @ClusteredYN AND IndexName = @IndexName

	    SET @InnerCounter = @InnerCounter + 1
	  END

      SET @OuterCounter = @OuterCounter + 1
    END

Rate

Share

Share

Rate