|
Execute the following
Microsoft SQL Server T-SQL database administration script in SSMS Query Editor to REBUILD all indexes in the AdventureWorks2008 sample database.
-- SQL Server 2008 script to REBUILD all indexes for all tables
USE AdventureWorks2008;
DECLARE @DatabaseName SYSNAME = DB_NAME(), @TableName VARCHAR(256)
DECLARE @FILLFACTOR INT = 85
DECLARE @DynamicSQL NVARCHAR(max) =
'DECLARE curAllTablesInDB CURSOR FOR SELECT TABLE_SCHEMA +
''.'' + TABLE_NAME AS TABLENAME
FROM ' + @DatabaseName + '.INFORMATION_SCHEMA.TABLES WHERE
TABLE_TYPE = ''BASE TABLE'''
BEGIN
EXEC sp_executeSQL @DynamicSQL -- create tables cursor
OPEN curAllTablesInDB
FETCH NEXT FROM curAllTablesInDB INTO @TableName
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @DynamicSQL = 'ALTER INDEX ALL ON ' + @TableName +
' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR,@FILLFACTOR) + ')'
PRINT @DynamicSQL
-- ALTER INDEX ALL ON Purchasing.ShipMethod REBUILD WITH (FILLFACTOR = 85)
EXEC sp_executeSQL @DynamicSQL
FETCH NEXT FROM curAllTablesInDB INTO @TableName
END -- cursor WHILE
CLOSE curAllTablesInDB
DEALLOCATE curAllTablesInDB
END
------------
-- INDEX REBUILD ONLINE
USE AdventureWorks2008;
GO
CREATE PROC sprocDBIndexREBUILD @FILLFACTOR INT = 90
AS
BEGIN
DECLARE @DatabaseName SYSNAME = DB_NAME(), @TableName VARCHAR(256)
DECLARE @DynamicSQL NVARCHAR(max) =
'DECLARE curAllTablesInDB CURSOR FOR SELECT TABLE_SCHEMA +
''.'' + TABLE_NAME AS TABLENAME
FROM ' + @DatabaseName + '.INFORMATION_SCHEMA.TABLES WHERE
TABLE_TYPE = ''BASE TABLE'''
BEGIN
EXEC sp_executeSQL @DynamicSQL -- create tables cursor
OPEN curAllTablesInDB
FETCH NEXT FROM curAllTablesInDB INTO @TableName
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @DynamicSQL = 'ALTER INDEX ALL ON ' + @TableName +
' REBUILD WITH (ONLINE=ON, FILLFACTOR = ' + CONVERT(VARCHAR,@FILLFACTOR) + ')'
-- PRINT @DynamicSQL
EXEC sp_executeSQL @DynamicSQL
FETCH NEXT FROM curAllTablesInDB INTO @TableName
END -- cursor WHILE
CLOSE curAllTablesInDB
DEALLOCATE curAllTablesInDB
END
END -- sproc
GO
Related articles:
SQL SERVER – ReIndexing Database Tables and Update Statistics on Tables
SQL Server script to rebuild all indexes for all tables and all databases
http://www.sqlusa.com/bestpractices2008/performancetuning/missingindexes/
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance
|
