Re-index All Tables in your SQL Database: Short & Dirty Script

I am not advocating that you use this in a production system, but if you’re looking to re-index all tables in your database (say on development or test), here is a short script for it:

exec sp_msforeachtable 'dbcc dbreindex(''?'', '''', 80)'

Note, the last parameter controls the fill factor, set it to 0 to re-use the last known fill factor or read more about it in Books Online. With sp_msforeachtable you’re looping through all tables in the database and executing the provided script. As you can see above you use a question mark (?) to substitute the table name. This is a decent alternative to manual looping through INFORMATION_SCHEMA.TABLES which is another way to get a list of all tables in your database.

If you were just looking to obtain a dbcc script you can run this command and it will generate the equivalent script that is run by the first command I showed:

	'dbcc dbreindex(''' + TABLE_NAME + ''', '''', 80)' 

Leave a Reply

Fill in your details below or click an icon to log in: Logo

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

Facebook photo

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

Connecting to %s

%d bloggers like this: