use AdventureWorks2008R2 go SELECT DB_NAME (PS.database_id) AS dbName, S.name AS SchemaName, O.name AS TableName, b.name, ps.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (DB_ID (), NULL, NULL, NULL, NULL ) AS ps INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID AND ps.index_id = b.index_id INNER JOIN sys.objects O ON PS.object_id = O.object_id INNER JOIN sys.schemas S ON S.schema_id = O.schema_id WHERE ps.avg_fragmentation_in_percent >= 20 -- Indexes having Fragmentation >=20 AND PS.index_type_desc IN ('CLUSTERED INDEX', 'NONCLUSTERED INDEX') -- Only get clustered and nonclustered indexes AND b.is_hypothetical = 0 -- Only real indexes AND O.type_desc = 'USER_TABLE' -- Restrict to user tables AND PS.page_count > 8 --- ignore tables less tha 64K ORDER BY ps.avg_fragmentation_in_percent DESC # Result # dbName SchemaName TableName name avg_fragmentation_in_percent # AdventureWorksDW2008R2 dbo FactResellerSales IX_FactResellerSales_ResellerKey 63.7755102040816 # AdventureWorksDW2008R2 dbo DimGeography PK_DimGeography_GeographyKey 30.7692307692308 -- To view fragmentation report at all databases on server use SQL below -- EXECUTE master.sys.sp_MSforeachdb 'USE [?]; SELECT DB_NAME (PS.database_id) AS dbName, S.name AS SchemaName, O.name AS TableName, b.name, ps.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (DB_ID (), NULL, NULL, NULL, NULL ) AS ps INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID AND ps.index_id = b.index_id INNER JOIN sys.objects O ON PS.object_id = O.object_id INNER JOIN sys.schemas S ON S.schema_id = O.schema_id WHERE ps.avg_fragmentation_in_percent >= 20 -- Indexes having Fragmentation >=20 AND PS.index_type_desc IN (''CLUSTERED INDEX'', ''NONCLUSTERED INDEX'') -- Only get clustered and nonclustered indexes AND b.is_hypothetical = 0 -- Only real indexes AND O.type_desc = ''USER_TABLE'' -- Restrict to user tables AND PS.page_count > 8 --- ignore tables less tha 64K ORDER BY ps.avg_fragmentation_in_percent DESC' # Result # dbName SchemaName TableName name avg_fragmentation_in_percent # msdb dbo FactResellerSales IX_FactResellerSales_ResellerKey 63.7755102040816 # AdventureWorks2008R2 dbo FactResellerSales IX_FactResellerSales_ResellerKey 63.7755102040816 # AdventureWorksDW2008R2 dbo DimGeography PK_DimGeography_GeographyKey 30.7692307692308