MS SQL server automatic re-index

This script is a simplified version of my "Fully automatic MS SQL database shrink and re-index script".  This script is for Production database.  I usually run it on my Production server periodically.

Since it is for Production, the threshold level for ScanDensity and LogicalScanFragmentation level can be fine tuned.

SET NOCOUNT ON

DECLARE @dbName varchar(40)

DECLARE @maxfrag DECIMAL

SET @maxfrag = 95    -- index will be reindex if ScanDensity < 95%

DECLARE @maxLogicalFrag DECIMAL

SET @maxLogicalFrag = 3    -- index will be reindex if Logical Scan Fragmentatio > 3%.  If you set a higher level, less index will be selected for reindex.

DECLARE @TableName varchar(255)

DECLARE @IndexName varchar(255)

DECLARE @TableId int

DECLARE @IndexId int

DECLARE @FileName varchar(50), @FileSize int, @FileGrowth int

DECLARE @sql nvarchar(1500)

DECLARE @starttime datetime

-----------

  

-- Create the temporary table to hold fragmentation information

select *  FROM tempdb.dbo.sysobjects T  where type = 'U' and name = 'fraglist'

    IF @@ROWCOUNT <> 0

drop table tempdb..fraglist

CREATE TABLE tempdb..fraglist (

ObjectName CHAR (255),

ObjectId INT,

IndexName CHAR (255),

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)

----------

DECLARE dbCursor CURSOR FOR

SELECT NAME --DB_NAME(dbid) AS DB_NAME

FROM master.dbo.sysdatabases

WHERE 

NAME IN ('DB1', 'DB2', 'DB2')

ORDER BY NAME DESC

--select * from master.dbo.sysdatabases

OPEN dbCursor

FETCH NEXT FROM dbCursor INTO @dbName

WHILE @@FETCH_STATUS = 0

BEGIN

     

    set @starttime = getdate()

    PRINT convert(varchar(30), @starttime, 120) + ': Reindex Job Started - ' + @dbName

    set @sql = 'USE ' + @dbName + '; SELECT T.name as tblname, I.name as indexname, T.id, I.indid '

    set @sql = @sql + ' INTO tempdb..temp_table '

    set @sql = @sql + ' FROM dbo.sysobjects T '

    set @sql = @sql + ' LEFT OUTER JOIN dbo.sysindexes I ON T.id = I.id '

    set @sql = @sql + ' where type = ''U'' and I.indid < 2 and rowcnt > 0 '

    set @sql = @sql + ' order by T.name, I.indid'

    print @sql

    select *  FROM tempdb.dbo.sysobjects T  where type = 'U' and name = 'temp_table'

    IF @@ROWCOUNT <> 0

    drop table tempdb..temp_table

    exec sp_executesql @sql

    --BREAK

    DECLARE TableCursor CURSOR FOR

    select * from tempdb..temp_table

   

    OPEN TableCursor

   

    truncate table tempdb..fraglist

    FETCH NEXT FROM TableCursor INTO @TableName, @IndexName , @TableId, @IndexId

    WHILE @@FETCH_STATUS = 0

    BEGIN

        -- Do the showcontig of all indexes of the table

        INSERT INTO tempdb..fraglist

        EXEC ('USE ' +@dbName + '; DBCC SHOWCONTIG (' + @TableName + ') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')

        FETCH NEXT FROM TableCursor INTO @TableName, @IndexName , @TableId, @IndexId

    END

    CLOSE TableCursor

    DEALLOCATE TableCursor

    -- break   

    DECLARE FragListCursor CURSOR FOR

    select ObjectName, IndexName, ObjectId, IndexId

        from tempdb..fraglist

        where IndexId < 255 and IndexId > 0

            and IndexName is not null

            and  (ScanDensity <= @maxfrag

                    or LogicalFrag >=@maxLogicalFrag)

    OPEN FragListCursor

    FETCH NEXT FROM FragListCursor INTO @TableName, @IndexName , @TableId, @IndexId

    WHILE @@FETCH_STATUS = 0

    BEGIN

        set @sql = 'DBCC DBREINDEX(''' + @dbName + '.dbo.' + rtrim(@TableName) + ''', ''' + rtrim(@IndexName) + ''' ,70) WITH NO_INFOMSGS '  -- fill factor = 70% some free space is needed since the Index will grow at Production

        print convert(varchar(30), getdate(), 120) +': ' + @sql

        exec sp_executesql @sql 

   

        set @sql =  'USE ' + @dbName + ';'+ 'update statistics ' + rtrim(@TableName) + ' ' + rtrim(@IndexName)

        exec sp_executesql @sql

   

        FETCH NEXT FROM FragListCursor INTO @TableName, @IndexName , @TableId, @IndexId

    END

   

    CLOSE FragListCursor

    DEALLOCATE FragListCursor

   PRINT convert(varchar(30), getdate(), 120) + ': Reindex Job Ended. It took ' + convert(varchar(6), datediff(hh, @starttime, getdate())) +' to complete the job.'

    FETCH NEXT FROM dbCursor INTO @dbName

END

CLOSE dbCursor

DEALLOCATE dbCursor