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

Vuyiswamb
Posted by in Sql Server category on for Advance level | Views : 6115 red flag

This Article will Show you , how to rebuild all the indexes in table in all the Databases in SQL 2005/200

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

 

 

Page copy protected against web site content infringement by Copyscape

About the Author

Vuyiswamb
Full Name: Vuyiswa Maseko
Member Level: NotApplicable
Member Status: Member,MVP,Administrator
Member Since: 7/6/2008 11:50:44 PM
Country: South Africa
Thank you for posting at Dotnetfunda [Administrator]
http://www.Dotnetfunda.com
Vuyiswa Junius Maseko is a Founder of Vimalsoft (Pty) Ltd (http://www.vimalsoft.com/) and a forum moderator at www.DotnetFunda. Vuyiswa has been developing for 16 years now. his major strength are C# 1.1,2.0,3.0,3.5,4.0,4.5 and vb.net and sql and his interest were in asp.net, c#, Silverlight,wpf,wcf, wwf and now his interests are in Kinect for Windows,Unity 3D. He has been using .net since the beta version of it. Vuyiswa believes that Kinect and Hololen is the next generation of computing.Thanks to people like Chris Maunder (codeproject), Colin Angus Mackay (codeproject), Dave Kreskowiak (Codeproject), Sheo Narayan (.Netfunda),Rajesh Kumar(Microsoft) They have made vuyiswa what he is today.

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)