The following SQL Server script enumerates the tables in a database and calls DBBC REINDEX on each one (with a fill factor of 80%). It then calls sp_updatestats too update the index statistics.
USE <database_name>
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
Print @TableName
DBCC DBREINDEX(@TableName,’ ‘,80)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
EXEC sp_updatestats
To avoid your queries from running under non-optimal execution plans, the script should be run periodically, typically weekly or nightly, when the the database is not in use.
Comments