Dynamic DBCC DBREINDEX

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