Get All Server Index Fragmentation

This stored procedure runs DBCC SHOWCONTIG against every database on the server (or just one, if you pass @DatabaseName), collects the fragmentation results for all indexes into a global temp table, and reports them sorted by scan density - with a ready-to-run DBCC DBREINDEX statement generated for each index. A good pre-maintenance snapshot of how fragmented your whole server is.

Example: EXEC GetAllServerIndexes -- or: EXEC GetAllServerIndexes @DatabaseName = 'MyDatabase'

CREATE PROCEDURE GetAllServerIndexes(@DatabaseName VarChar(300) = '') AS 
BEGIN
	SET NOCOUNT ON;

	DECLARE @SQL VarChar(8000)
	DECLARE @DB VarChar(300)
	DECLARE @DBID BigInt
	DECLARE @objectID BigInt

	CREATE TABLE ##Contig
	(
		DatabaseName VarChar(255),
		DatabaseID BigInt,
		ObjectName VarChar(255),
		ObjectId INT,
		IndexName VarChar(255),
		IndexId INT,
		Lvl INT,
		CountPages INT,
		CountRows INT,
		MinRecSize INT,
		MaxRecSize INT,
		AvgRecSize INT,
		ForRecCount INT,
		Extents INT,
		ExtentSwitches INT,
		AvgFreeBytes INT,
		AvgPageDensity INT,
		ScanDensity DECIMAL,
		BestCount INT,
		ActualCount INT,
		LogicalFrag DECIMAL,
		ExtentFrag DECIMAL
	)

	DECLARE Databases CURSOR FAST_FORWARD FOR
		SELECT
			SysDB.name
		FROM
			master..sysdatabases AS SysDB
		WHERE
			(SysDB.name = @DatabaseName OR @DatabaseName = '')
			AND SysDB.name <> 'TempDB'
		ORDER BY
			SysDB.name
	OPEN Databases

	FETCH FROM Databases INTO @DB

	WHILE(@@FETCH_STATUS = 0)
	BEGIN
		----------------------------------------------------------------------------------------------------------------

		SET @DBID = DB_ID(@DB)

		SET @SQL =
			'USE [' + @DB + '];' +
			'CREATE TABLE #FragList' +
			'(' +
			'\tObjectName VarChar (255),' +
			'\tObjectId INT,' +
			'\tIndexName VarChar (255),' +
			'\tIndexId INT,' +
			'\tLvl INT,' +
			'\tCountPages INT,' +
			'\tCountRows INT,' +
			'\tMinRecSize INT,' +
			'\tMaxRecSize INT,' +
			'\tAvgRecSize INT,' +
			'\tForRecCount INT,' +
			'\tExtents INT,' +
			'\tExtentSwitches INT,' +
			'\tAvgFreeBytes INT,' +
			'\tAvgPageDensity INT,' +
			'\tScanDensity DECIMAL,' +
			'\tBestCount INT,' +
			'\tActualCount INT,' +
			'\tLogicalFrag DECIMAL,' +
			'\tExtentFrag DECIMAL' +
			') ' +

			'INSERT INTO #FragList ' +
			'EXEC(''DBCC SHOWCONTIG WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS'') ' +
			';' +
			'INSERT INTO ##Contig  ' +
			'SELECT ' +
				'''' + @DB + ''', ' +
				'''' + Convert(VarChar(32), @DBID) + ''', ' +
				'S.ObjectName, ' +
				'S.ObjectId, ' +
				'S.IndexName, ' +
				'S.IndexId, ' +
				'S.Lvl, ' +
				'S.CountPages, ' +
				'S.CountRows, ' +
				'S.MinRecSize, ' +
				'S.MaxRecSize, ' +
				'S.AvgRecSize, ' +
				'S.ForRecCount, ' +
				'S.Extents, ' +
				'S.ExtentSwitches, ' +
				'S.AvgFreeBytes, ' +
				'S.AvgPageDensity, ' +
				'S.ScanDensity, ' +
				'S.BestCount, ' +
				'S.ActualCount, ' +
				'S.LogicalFrag, ' +
				'S.ExtentFrag ' +
			'FROM ' +
				'#FragList AS S' +
			'; ' +
			'DROP TABLE #FragList'
		EXEC(@SQL)

		----------------------------------------------------------------------------------------------------------------
		FETCH FROM Databases INTO @DB
	END

	CLOSE Databases DEALLOCATE Databases

	----------------------------------------------------------------------------------------------------------------

	SELECT
		DatabaseName,
		ObjectName,
		IndexName,
		AvgPageDensity,
		ScanDensity,
		BestCount,
		ActualCount,
		LogicalFrag,
		ExtentFrag,
		'USE [' + DatabaseName + ']; DBCC DBREINDEX(''' + ObjectName + ''', ''' + IndexName + ''');' AS RebuildSQL
		--'ALTER INDEX [' + IndexName + '] ON [' + DatabaseName + ']..[' + ObjectName + '] ' +
		--'REBUILD WITH (SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF);' AS NewMethod
	FROM
		##Contig
	WHERE
		ObjectName NOT LIKE 'sys%'
	ORDER BY
		DatabaseName ASC,
		ScanDensity DESC

	----------------------------------------------------------------------------------------------------------------

SELECT * FROM ##Contig

	DROP TABLE ##Contig
END

GO