How to Rebuilt all indexes in all Databases in SQL 2000/2005

 

Introduction

There was a time where I inherited a project that uses a SQL Database that used indexes. The Database will work fine and the queries will look healthy. But after a certain long period the queries will become slow. Someone might ask what has changed. Well nothing has change. I used SQL Compare to check the difference between the Two Databases and there was none, the other one was working and the other was slowJ.  I tried so much to optimize the queries, and I came I across the rebuilt option in the on the indexes when I browse the table in SQL Management Studio. I rebuild the indexes and something change, the speed was great again, but not in all part, only in table that got index rebuild. Now this means the indexes need to be maintained.

Solution

I goggled and I came across a great post by Edgewood Solutions Engineers. This will allow you to Rebuilt all the Indexes in all the Databases in a SQL Server. The nice thing here is that you can put this in a SSIS package that can run on certain days and certain time.

Using the code

I have prepared a stored procedure that will help us do everything on one place

 

/*THIS SP WILL REBUILT ALL THE INDEXES IN ALL TABLES IN ALL DATABASES

THIS WILL BE CALLED FROM AN SSIS PACKAGE. THIS WILL USED IN CLIENTS TO REBUILT INDEXES AND IT WILL BE

RAN EVERY FRIDAY NIGHT

*/

 

ALTER PROC DATABASE_MAINTANANCE_PLAN

AS

 

DECLARE @Database VARCHAR(255) 

DECLARE @Table VARCHAR(255)

DECLARE @cmd NVARCHAR(500)

DECLARE @fillfactor INT

 

SET @fillfactor = 90

 

DECLARE DatabaseCursor CURSOR FOR

SELECT name FROM master.dbo.sysdatabases 

WHERE name NOT IN ('master','model','msdb','tempdb','distrbution') 

ORDER BY 1

 

OPEN DatabaseCursor

 

FETCH NEXT FROM DatabaseCursor INTO @Database

WHILE @@FETCH_STATUS = 0

BEGIN

 

   SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT table_catalog + ''.'' + table_schema + ''.'' + table_name as tableName 

                    FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE''' 

 

   -- create table cursor

   EXEC (@cmd)

   OPEN TableCursor 

 

   FETCH NEXT FROM TableCursor INTO @Table 

   WHILE @@FETCH_STATUS = 0 

   BEGIN 

 

       -- SQL 2000 command

       --DBCC DBREINDEX(@Table,' ',@fillfactor) 

       

       -- SQL 2005 command

       SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'

       EXEC (@cmd)

 

       FETCH NEXT FROM TableCursor INTO @Table 

   END 

 

   CLOSE TableCursor 

   DEALLOCATE TableCursor

 

   FETCH NEXT FROM DatabaseCursor INTO @Database

END

CLOSE DatabaseCursor 

DEALLOCATE DatabaseCursor

GO

sp_updatestats

 

Conclusion

You can use SSIS to do this, If you need more in for SSIS you can just Google “Introduction to SSIS in SQL”

Thank you

 

Vuyiswa Maseko

 

 

If you like this article, subscribe to our RSS Feed. You can also subscribe via email to our Interview Questions, Codes and Forums section.