T-SQL: Rebuild all user table indexes dynamically

The more complex the schema, the more indexes you probably have on your tables. Here’s some T-SQL to rebuild all those indexes without having to write out the SQL for each one.

 
DECLARE @TABLE VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
 
DECLARE @fillfactor INT
SET @fillfactor = 92
 
DECLARE table_cursor CURSOR
FOR
SELECT table_catalog + '.' + table_schema + '.' + TABLE_NAME AS full_table_name
FROM MyDatabase.INFORMATION_SCHEMA.TABLES
WHERE table_type = 'BASE TABLE'
 
OPEN table_cursor
 
FETCH NEXT FROM table_cursor INTO @TABLE
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd =	'ALTER INDEX ALL ON ' + @TABLE + ' ' +
'REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@cmd)
 
FETCH NEXT FROM table_cursor INTO @TABLE
END
CLOSE table_cursor
DEALLOCATE table_cursor

You will most likely want to adjust the fill factor and/or the actual ALTER INDEX statement to suit the indexes that you have defined on your tables.

Also, I don’t use DBCC DBREINDEX because it has been deprecated by Microsoft.

Hope this helps!

This entry was posted in T-SQL. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *