Rebuild all Indexes in SQL Database

Here is a script to rebuild all of your indexes on your database. I find that indexes are often ignored once an application moves to production. It’s a good idea to evaluate your usage patterns and setup automated maintenance jobs to proactively rebuild indexes before fragmentation has a chance to adversely effect performance. This is especially important if you are using Guids as primary keys and don’t move the clustered index to a more logically sequential column.

 -- Show fragmentation for all tables
EXEC sp_MSforeachtable @command1="print '?' DBCC SHOWCONTIG('?')"

--Rebuild all indexes (note this method locks the tables while the indexes are being rebuilt)

USE [myDatabase]
DECLARE @TableName varchar(255)

SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'

OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName

DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName

CLOSE TableCursor

Hope this helps someone.

Happy coding,
Tom Hundley

What are your thoughts?

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.