Skip to content

Reindex All Tables in a Database

2008 August 22
by Shannon Lowder

I just wanted to share a quick script that I used to help re-index all the tables in my database. If you have any questions about it, please let me know!

USE 
GO
DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'base table'

OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
	WHILE @@FETCH_STATUS = 0
	BEGIN
		PRINT 'Reindexing: ' + @TableName
		DBCC DBREINDEX(@TableName,' ',90)
	
		FETCH NEXT FROM TableCursor INTO @TableName
	END
	
CLOSE TableCursor
DEALLOCATE TableCursor 
5 Responses leave one →
  1. Vishal Thaker permalink
    June 12, 2012

    Shannon,

    What are the pros and cons of this script. Basically, I want to know if I can run this script blindly whenever I see a performance (slowness) issues.

    • June 22, 2012

      I wouldn’t run it blindly. This is more for a proactive situation. If you know you get a lot of reads on your tables, and you also do a lot of inserting, you may find your indexes grow really fragmented. You could either put in a maintenance plan to handle it, or you could use this script.

      I shared the script because I was in a situation where I needed to re-index everything as quickly as I could…. and this was the script that came from that.

      Email me at slowder@hivemindconsulting.net, we can talk about your specific situation and I can give you a few hints of things to try every time.

      • Zigmaphi permalink
        October 17, 2013

        I’ve been trying to decide which DBs to reorganize and which to re-index. I’m not sure if i should even reorganize.
        http://msdn.microsoft.com/en-us/library/ms189858(v=SQL.100).aspx says i should only reindex if fragmentation is less than 30%. should i just follow that.

        • October 29, 2013

          I usually follow Michelle Ufford’s indexing stragegies her latest script can be found here. If the fragmentation is over 30, just rebuild the index, the cost should be lower than trying to re-organize. In the past I recall anything less than 5% fragmentation should be ignored, but it looks like Michelle has upped that threshold to 10%. I would say if you have the time in your maintenance window to rebuild those indexes between 5 and 30%, go for it. If not, I’d sacrifice rebuilding the 5% fragmentation indexes before sacrificing those with more fragmentation.

          When you start getting tables with BILLIONS of rows, you might want to consider something a little more drastic. I start partitioning the data either with actual partitioning, or adding where clauses to my indexes, so I can rebuild sub-sets of the indexes more quickly.

  2. May 7, 2015

    Thanks for this useful sql script. it works perfectly.

    Regards

Leave a Reply

Note: You can use basic XHTML in your comments. Your email address will never be published.

Subscribe to this comment feed via RSS