CreateDBREINDEX

------------------------------------------------------------------------
--  Name   :  CreateDBREINDEX.sql  
--  Author :  Jeff Weisbecker  October 1, 2003
--  RDBMS  :  SQL Server 2000
--  Desc   :  This script will generate a set of DBCC DBREINDEX commands
--            that can be executed to rebuild the indexes.  Additional  
--            information is provided to assist with sizing of the fill
--            factors.
--
--            To execute the DBREINDEX commands the line will have to 
--            be uncommented (It is commented out to prevent accidental
--            execution).  Also, the value for the fill factor is not
--            specified in the command.  If you do not want to change 
--            the fill factor remove the last comma in the DBREINDEX
--            statement.  See Books Online for proper syntax.
--
--            @MinPages -  You may not want to build a reindexing
--            script for small indexes.  This value specifies the 
--            minimum number the pages the index must contain.  The
--            number of rows is not a good indicator of the size of the
--            index. 
--
--            @MaxScanDensity - You may not want to build a reindexing
--            script for indexes with a high scan density.  For example,
--            you may not want to rebuild indexes that have a scan
--            density of 90% or higher so you could set this variable
--            to 90.
--
--            Comments : I created this script to help assist with 
--            fill factors.  Heap tables are not included in the results, 
--            but indexes on heaps will be.  As always, use a script
--            with caution.  
--            
------------------------------------------------------------------------
SET NOCOUNT ON
--
DECLARE @SQLString      VARCHAR(2000)    -- String used to hold SQL Statements to be executed.
DECLARE @ObjectID       INT              -- Not used, but may be useful for enhancements
DECLARE @TableName      VARCHAR(120)     -- Table name
DECLARE @TableSchema    VARCHAR(40)      -- Owner of the table
DECLARE @MinPages       INT              -- The minimum number of pages needed reindex
DECLARE @MaxScanDensity INT              -- The maximum scan density that will be reindexed
--
SET @MinPages       = 0                  -- Modify to reduce results 
SET @MaxScanDensity = 100                -- Modify to reduce results
--
CREATE TABLE #TempForShowContig(ObjectName      VARCHAR (60),
                                ObjectId        INT,
                                IndexName       VARCHAR (60),
                                IndexId         INT,
                                Lvl             INT,
                                CountPages      INT,
                                CountRows       INT,
                                MinRecSize      INT,
                                MaxRecSize      INT,
                                AvgRecSize      INT,
                                ForRecCount     INT,
                                Extents         INT,
                                ExtentSwitches  INT,
                                AvgFreeBytes    INT,
                                AvgPageDensity  INT,
                                ScanDensity     DECIMAL,
                                BestCount       INT,
                                ActualCount     INT,
                                LogicalFrag     DECIMAL,
                                ExtentFrag      DECIMAL)
--
CREATE TABLE #TempForTableName (TableName   VARCHAR(120),
                                TableSchema VARCHAR(40),
                                ObjectID    INT)
--
DECLARE c_table CURSOR FOR
   SELECT TableName, TableSchema, ObjectID
   FROM #TempForTableName

SET @SQLString = 'SELECT o.name , USER_NAME(o.uid), o.id '   +
                 'FROM sysobjects o, '                       + 
                 '     sysindexes i   '                      +
                 'WHERE o.type = ''U'''                      +
                 '  AND o.id = i.id '                        +
                 '  AND i.indid IN (0,1) '                   +
                 '  AND o.name != ''dtproperties''' 

INSERT #TempForTableName
EXECUTE(@SQLString)
------------------------------------------------------------------------
OPEN c_table
FETCH NEXT FROM c_table INTO @TableName, @TableSchema, @ObjectID
   WHILE @@FETCH_STATUS = 0
      BEGIN 
          INSERT #TempForShowContig
          EXEC ('DBCC SHOWCONTIG (''[' + @TableSchema +'].['+ @TableName + ']'') WITH ALL_INDEXES, TABLERESULTS, NO_INFOMSGS')
          FETCH NEXT FROM c_table INTO @TableName, @TableSchema, @ObjectID
      END 
   CLOSE c_table
--
-------------------------------------------------------------------------
DEALLOCATE c_table
-------------------------------------------------------------------------
-- The following SELECT can be uncommented to display the SHOWCONTIG 
-- results for all of the tables and indexes.
-------------------------------------------------------------------------
-- SELECT '-- ', * FROM #TempForShowContig
--
PRINT '---------------------------------'
PRINT '-- Date:     ' + CAST(GETDATE() AS CHAR(20))
PRINT '-- Database: ' + DB_NAME()
PRINT '---------------------------------'
PRINT 'USE ' + DB_NAME()
PRINT 'GO'
--
SELECT '---------------------------------'                         + CHAR(10) +
       '---------------------------------'                         + CHAR(10) +
       '-- Table          : ' + RTRIM(t.ObjectName)                + CHAR(10) +
       '-- Owner          : ' + RTRIM(USER_NAME(o.uid))            + CHAR(10) +
       '-- Index          : ' + RTRIM(t.IndexName)                 + CHAR(10) +
       '-- ScanDensity    : ' + CAST(t.ScanDensity    AS CHAR(3))  + CHAR(10) +
       '-- FillFactor     : ' + CAST(i.OrigFillFactor AS CHAR(3))  + CHAR(10) +
       '-- AvgPageDensity : ' + CAST(t.AvgPageDensity AS CHAR(5))  + CHAR(10) +
       '-- AvgRecordSize  : ' + CAST(t.AvgRecSize     AS CHAR(10)) + CHAR(10) +
       '-- Row Count      : ' + CAST(t.CountRows AS VARCHAR(10))   + CHAR(10) +
       '-- Page Count     : ' + CAST(t.CountPages AS VARCHAR(10))  + CHAR(10) +
       CAST('-- DBCC DBREINDEX(''' + RTRIM(USER_NAME(o.uid)) + '.' + RTRIM(t.ObjectName) 
                             + ''',''' + RTRIM(t.IndexName) + ''',)'  AS VARCHAR(180)) 
FROM #TempForShowContig  t,
     sysindexes          i,
     sysobjects          o 
WHERE  i.id   = t.ObjectId  
  AND  i.name = t.IndexName 
  AND  i.id   = o.id
  AND  t.ScanDensity  < @MaxScanDensity
  AND  t.CountPages   > @MinPages
------------
DROP TABLE #TempForShowContig 
DROP TABLE #TempForTableName
--
SET NOCOUNT OFF