As a database administrator, one of the common questions you may encounter in your early career is how to reindex every table in a SQL Server database. Reindexing is an important maintenance task that helps optimize the performance of your database by rebuilding the indexes.

Here is a script that you can use to reindex every table in a SQL Server database:

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

It’s important to note that the above script is for SQL Server 2014 and later versions. If you are using SQL Server 2005, 2008, or 2012, you can use the following script:

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

If you are using SQL Server 2000, you can use the following script:

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

These scripts have been tested and proven to work effectively. However, it’s always a good practice to backup your database before performing any maintenance tasks.

For further information and related blog posts, you can refer to the following resources:

• 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

Reindexing your database tables is an essential task to maintain the performance and efficiency of your SQL Server database. By using the provided scripts and following best practices, you can ensure that your database is running optimally.