SQL Server DMV Query to Output Index Fragmentation by Database / Table

The following SQL Server dynamic management view (DMV) query will return detailed information about the fragmentation level of a database. Depending on the size of the DB, this may take some time to execute;

USE AdventureWorks
GO
SELECT object_name(IPS.object_id) AS [TableName], 
SI.name AS [IndexName], 
IPS.Index_type_desc, 
IPS.avg_fragmentation_in_percent, 
IPS.avg_fragment_size_in_pages, 
IPS.avg_page_space_used_in_percent, 
IPS.record_count, 
IPS.ghost_record_count,
IPS.fragment_count, 
IPS.avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats(db_id(N'AdventureWorks'), NULL, NULL, NULL , 'DETAILED') IPS
JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id
JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id
WHERE ST.is_ms_shipped = 0
ORDER BY 1,5
GO

To find the fragmentation level of a database table, you can also use the following;

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'AdventureWorks');
SET @object_id = OBJECT_ID(N'Production.BillOfMaterials');
IF @object_id IS NULL 
BEGIN
PRINT N'Invalid object';
END
ELSE
BEGIN
SELECT IPS.Index_type_desc, 
IPS.avg_fragmentation_in_percent, 
IPS.avg_fragment_size_in_pages, 
IPS.avg_page_space_used_in_percent, 
IPS.record_count, 
IPS.ghost_record_count,
IPS.fragment_count, 
IPS.avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'DETAILED') AS IPS;
END
GO