Rebuild all indexes in a Database

Question

I have a very large SQL Server 2008 R2 database (1.5TB) and will be copying some data from column to column within the same table. I've been told that the schema has a large number of indexes and was wondering if there is a default query or script that will rebuild all the indexes. Have also been advised to update the statistics at the same time?

Each of the 30 tables has one clustered index and 13x non-clustered indexes

Thanks.

Answer 1

Try the following script:

Exec sp_msforeachtable 'SET QUOTED_IDENTIFIER ON; ALTER INDEX ALL ON ? REBUILD' GO 

Also

I prefer(After a long search) to use the following script, it contains @fillfactor determines how much percentage of the space on each leaf-level page is filled with data.

DECLARE @TableName VARCHAR(255) DECLARE @sql NVARCHAR(500) DECLARE @fillfactor INT SET @fillfactor = 80  DECLARE TableCursor CURSOR FOR SELECT QUOTENAME(OBJECT_SCHEMA_NAME([object_id]))+'.' + QUOTENAME(name) AS TableName FROM sys.tables OPEN TableCursor FETCH NEXT FROM TableCursor INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')' EXEC (@sql) FETCH NEXT FROM TableCursor INTO @TableName END CLOSE TableCursor DEALLOCATE TableCursor GO 

for more info, check the following link:

https://blog.sqlauthority.com/2009/01/30/sql-server-2008-2005-rebuild-every-index-of-all-tables-of-database-rebuild-index-with-fillfactor/

and if you want to Check Index Fragmentation on Indexes in a Database, try the following script:

SELECT dbschemas.[name] as 'Schema', dbtables.[name] as 'Table', dbindexes.[name] as 'Index', indexstats.avg_fragmentation_in_percent, indexstats.page_count FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id] INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id] INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id] AND indexstats.index_id = dbindexes.index_id WHERE indexstats.database_id = DB_ID() AND dbtables.[name] like '%%' ORDER BY indexstats.avg_fragmentation_in_percent desc 

For more information, Check the following link:

http://www.schneider-electric.com/en/faqs/FA234246/

Answer 2

Replace the "YOUR DATABASE NAME" in the query below.

    DECLARE @Database NVARCHAR(255)        DECLARE @Table NVARCHAR(255)       DECLARE @cmd NVARCHAR(1000)        DECLARE DatabaseCursor CURSOR READ_ONLY FOR       SELECT name FROM master.sys.databases        WHERE name IN ('YOUR DATABASE NAME')  -- databases     AND state = 0 -- database is online     AND is_in_standby = 0 -- database is not read only for log shipping     ORDER BY 1        OPEN DatabaseCursor        FETCH NEXT FROM DatabaseCursor INTO @Database       WHILE @@FETCH_STATUS = 0       BEGIN           SET @cmd = 'DECLARE TableCursor CURSOR READ_ONLY 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           BEGIN TRY                 SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD'               PRINT @cmd -- uncomment if you want to see commands              EXEC (@cmd)            END TRY           BEGIN CATCH              PRINT '---'              PRINT @cmd              PRINT ERROR_MESSAGE()               PRINT '---'           END CATCH            FETCH NEXT FROM TableCursor INTO @Table           END            CLOSE TableCursor           DEALLOCATE TableCursor           FETCH NEXT FROM DatabaseCursor INTO @Database       END       CLOSE DatabaseCursor        DEALLOCATE DatabaseCursor