Interview Question of the Week #007 – How to Reindex Every Table of the Database?

Some questions are extremely popular questions and they never get old. Here is one such question which I see very often asked to DBAs in their early career.

Question: How to re-index every table of the database?

Answer: Well, The answer of this question can be only given in the form of the script.

Solarwinds

For SQL Server 2014 and later version

DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName
FROM sys.tables
WHERE is_memory_optimized = 0
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO

(Remember that alter index will fail on in-memory table, hence they needs to be excluded)

For SQL Server 2005, 2008 and 2012 versions

DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO

For SQL Server 2000 version

DECLARE @MyTable VARCHAR(255)
DECLARE myCursor
CURSOR FOR
SELECT table_name
FROM information_schema.tables
WHERE table_type = 'base table'
OPEN myCursor
FETCH NEXT
FROM myCursor INTO @MyTable
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Reindexing Table:  ' + @MyTable
DBCC DBREINDEX(@MyTable, '', 80)
FETCH NEXT
FROM myCursor INTO @MyTable
END
CLOSE myCursor
DEALLOCATE myCursor
EXEC sp_updatestats
GO

Well, there are many different methods and many different variations out there for this script, however, above script has always worked for me and I trust them.

Here are few related blog posts one should refer for further information.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
Previous Post
SQL SERVER – Msg 8152, Level 16, State 14 – String or Binary Data Would be Truncated
Next Post
SQL SERVER – Installation Error – INSTALLSHAREDDIR parameter is not valid because this directory is compressed or is in a compressed directory

Related Posts

9 Comments. Leave new

  • You could really hurt your overall performance by resetting the fillfactor on all indexes to only 80%, particularly on large+ tables. Why not just leave the fillfactor out, leaving it unchanged, rather than forcing a single value across all indexes?

    Reply
    • I agree Scott. The script can be modified based on business requirement. There is no one-size-fit-all script which I can make.

      Reply
      • Hello Sir ,
        Can i use dbreindex dbcc command

        alter proc usp_defrag as
        begin
        CREATE TABLE #TMPFRAG (
        ObjectName VARCHAR (255),
        ObjectId INT,
        IndexName VARCHAR (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 INT,
        BestCount INT,
        ActualCount INT,
        LogicalFrag INT,
        ExtentFrag INT)
        INSERT INTO #TMPFRAG
        EXECUTE (‘DBCC SHOWCONTIG WITH TABLERESULTS’)

        DECLARE @OBJid INT,@OBJNAME VARCHAR(30),@scanden int
        declare c1 CURSOR
        for select ObjectId,OBJECTNAME,SCANDENSITY FROM #TMPFRAG where ScanDensity <=50
        OPEN C1
        FETCH NEXT FROM C1 INTO @OBJID,@OBJNAME,@SCANDEN
        WHILE(@@FETCH_STATUS=0)
        BEGIN
        dbcc dbreindex(@OBJNAME);
        FETCH NEXT FROM C1 INTO @OBJID,@OBJNAME,@SCANDEN

        END
        CLOSE C1
        DEALLOCATE C1
        end

        exec usp_defrag

  • Following script works fine for me:

    DECLARE @sql VARCHAR(500)
    DECLARE @fillfactor INT
    SET @fillfactor = 80
    SET @sql = ‘ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR = ‘ + CONVERT(VARCHAR(3),@fillfactor) + ‘)’
    exec sp_MSforeachtable @command1=@sql

    Reply
  • The sp_msforeachtable method also works.

    Examples:

    exec sp_msforeachtable “set quoted_identifier on alter index all on ? rebuild”

    exec sp_msforeachtable “set quoted_identifier on alter index all on ? reorganize”

    exec sp_msforeachtable “set quoted_identifier on alter index all on ? rebuild with ( fillfactor = 100 )”

    Reply
  • I use an application that has a lot of data. The application is doing heavy calculations that on a big database can take hours sometimes. Index defragmentation has a major impact on calculations performance, so a index maintenance is periodically required. I used many index defragmentation scripts, but this one had the biggest impact on performance for my application. After running this script, my calculation time was shortened to less than half of how long usually took to complete. So many thanks for this wonder script.

    Reply
    • You should reindex the table which had a lot of changes. If no data is changed, don’t run reindex.

      Reply

Leave a Reply

Menu