Wednesday, 27 March 2013

SQL Server : Automatically Reindex All Tables in a SQL Server Database

This script will automatically reindex all indexes the tables in a selected database. When DBCC DBREINDEX is used to rebuild indexes, bear in mind that as the indexes on a specific table are being rebuilt, that the table becomes unavailable for use by your users.



USE DatabaseName

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
DBCC DBREINDEX(@TableName,’ ‘,90)
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor





the same can be done for all available schemas in database-


USE DatabaseName

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR
SELECT table_schema+'.'+table_name as table_name FROM information_schema.tables
WHERE table_type = 'BASE TABLE'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,’ ‘,90)
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor




or more efficiently, it all can be done with-

EXEC sp_MSforeachtable @command1 = "print '?' DBCC DBREINDEX ('?','', 90)"

No comments:

Post a Comment