Rebuilding Indexes In One Database

Standard

I came across a question that we needed to rebuild the indexes only on a particular database.

The script  I created selects all the tables from a certain database and puts them into a cursor. Than the script runs the index command with the table in line.

DECLARE @databasename VARCHAR(20)
DECLARE @fulltablename VARCHAR(50)

SET @databasename = '[yourdatabase]'

DECLARE tables CURSOR FOR
SELECT name AS tblname
FROM @database.dbo.sysobjects
WHERE (xtype = 'U')

OPEN tables

FETCH NEXT FROM tables INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fulltablename = @databasename + '.' + @tablename
ALTER INDEX ALL ON @fulltablename
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON);
GO
END

CLOSE db_cursor
DEALLOCATE db_cursor

Leave a Reply

Your email address will not be published.