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.

For SQL Server 2014 and later version

DECLARE @TableName VARCHAR(255)DECLARE @sql NVARCHAR(500)DECLARE @fillfactor INT
SET @fillfactor = 80DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableNameFROM sys.tablesWHERE is_memory_optimized = 0OPEN TableCursorFETCH NEXT FROM TableCursor INTO @TableNameWHILE @@FETCH_STATUS = 0BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' +CONVERT(VARCHAR(3),@fillfactor) + ')'EXEC (@sql)FETCH NEXT FROM TableCursor INTO @TableNameEND
CLOSE TableCursorDEALLOCATE 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 = 80DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableNameFROM sys.tablesOPEN TableCursorFETCH NEXT FROM TableCursor INTO @TableNameWHILE @@FETCH_STATUS = 0BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' +CONVERT(VARCHAR(3),@fillfactor) + ')'EXEC (@sql)FETCH NEXT FROM TableCursor INTO @TableNameEND
CLOSE TableCursorDEALLOCATE TableCursor
GO

For SQL Server 2000 version

DECLARE @MyTable VARCHAR(255)DECLARE myCursorCURSOR FOR
SELECT table_nameFROM information_schema.tablesWHERE table_type = 'base table'OPEN myCursorFETCH NEXTFROM myCursor INTO @MyTableWHILE @@FETCH_STATUS = 0BEGIN
PRINT 'Reindexing Table:  ' + @MyTableDBCC DBREINDEX(@MyTable, '', 80)FETCH NEXTFROM myCursor INTO @MyTableEND
CLOSE myCursorDEALLOCATE myCursorEXEC sp_updatestatsGO

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.

• SQL SERVER – Online Index Rebuilding Index Improvement in SQL Server 2012
• SQL SERVER – 2008 – 2005 – Rebuild Every Index of All Tables of Database – Rebuild Index with FillFactor
• SQL SERVER – ReIndexing Database Tables and Update Statistics on Tables