Categories
Database Indexes Microsoft SQL Server

Rebuild and Reorganize all indexes within a database

If you want to REBUILD and REORGANIZE all your indexes in a database, you have to use this query:

Rebuild:

DECLARE @Loop int
DECLARE @Loop2 int
DECLARE @MaxLoop int
DECLARE @MaxLoop2 int
DECLARE @DBName varchar(300)
DECLARE @SQL varchar(max)

SET @Loop = 1
SET @DBName = ''

set nocount on
SET @MaxLoop =  (select count([name]) FROM sys.databases where [name] like '%')
WHILE @Loop <= @MaxLoop
    BEGIN
        SET @DBName = (select TableWithRowsNumbers.name from (select ROW_NUMBER() OVER (ORDER by [name]) as Row,[name] FROM sys.databases where [name] like 'Z%' ) TableWithRowsNumbers where Row = @Loop)
        SET @SQL = 'USE [' + @DBName + ']'
        print (@SQL)
		exec (@SQL)
		
		SET @Loop2 = 1

		SET @MaxLoop2 =  (
		SELECT 
			Count(indexstats.database_id) 
			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()
		);
		WHILE @Loop2 <= @MaxLoop2
			begin
			set @SQL = 
			(
				select xx.cmd from 
				(
					SELECT 
					ROW_NUMBER() OVER (ORDER by indexstats.avg_fragmentation_in_percent) as Row,
					indexstats.database_id ,
					dbschemas.[name] as 'Schema',
					dbtables.[name] as 'Table',
					dbindexes.[name] as 'Index',
					indexstats.avg_fragmentation_in_percent,
					indexstats.page_count,
					'alter index ' +  dbindexes.[name] + ' on ' + dbschemas.[name] + '.' + dbtables.[name] + ' rebuild;' as cmd
					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()
				) xx where Row = @Loop2
			)
			exec (@SQL)
			print (@SQL)
			
			set @Loop2 = @Loop2+1;
		end
        set @Loop = @Loop + 1;
    END

Reorganize:

DECLARE @Loop int
DECLARE @Loop2 int
DECLARE @MaxLoop int
DECLARE @MaxLoop2 int
DECLARE @DBName varchar(300)
DECLARE @SQL varchar(max)

SET @Loop = 1
SET @DBName = ''

set nocount on
SET @MaxLoop =  (select count([name]) FROM sys.databases where [name] like '%')
WHILE @Loop <= @MaxLoop
    BEGIN
        SET @DBName = (select TableWithRowsNumbers.name from (select ROW_NUMBER() OVER (ORDER by [name]) as Row,[name] FROM sys.databases where [name] like 'Z%' ) TableWithRowsNumbers where Row = @Loop)
        SET @SQL = 'USE [' + @DBName + ']'
        print (@SQL)
		exec (@SQL)
		
		SET @Loop2 = 1

		SET @MaxLoop2 =  (
		SELECT 
			Count(indexstats.database_id) 
			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()
		);
		WHILE @Loop2 <= @MaxLoop2
			begin
			set @SQL = 
			(
				select xx.cmd from 
				(
					SELECT 
					ROW_NUMBER() OVER (ORDER by indexstats.avg_fragmentation_in_percent) as Row,
					indexstats.database_id ,
					dbschemas.[name] as 'Schema',
					dbtables.[name] as 'Table',
					dbindexes.[name] as 'Index',
					indexstats.avg_fragmentation_in_percent,
					indexstats.page_count,
					'alter index ' +  dbindexes.[name] + ' on ' + dbschemas.[name] + '.' + dbtables.[name] + ' reorganize;' as cmd
					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()
				) xx where Row = @Loop2
			)
			exec (@SQL)
			print (@SQL)
			
			set @Loop2 = @Loop2+1;
		end
        set @Loop = @Loop + 1;
    END

Leave a Reply

Your email address will not be published. Required fields are marked *