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 @TableNameVARCHAR(255)
DECLARE @IndexNameVARCHAR(255)
DECLARE @OrigFillFactorINTEGER
DECLARE @OuterCounterINTEGER
DECLARE @InnerCounterINTEGER
DECLARE @OuterLoopINTEGER
DECLARE @InnerLoopINTEGER
DECLARE @ClusteredYNINTEGER
DECLARE @strSQLNVARCHAR(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