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