Find Table Fragmentation

Find Table Fragmentation

DECLARE @DATABASE   VARCHAR(255)
DECLARE @TableName  VARCHAR(255)
DECLARE @IndexName  VARCHAR(255)

SET @DATABASE   = 'SQLServerPlanet'
SET @TableName  = 'Users'
SET @IndexName  = NULL

SELECT
    avg_fragmentation_in_percent
    ,page_count
FROM sys.dm_db_index_physical_stats
(
    DB_ID(@DATABASE)
    ,OBJECT_ID(@TableName)
    ,OBJECT_ID(@IndexName)
    ,NULL
    ,NULL
)

Or you can still do it the old fashioned way. Just substitute the name of the table or index below. It should not be in quotes.

–Just the table
DBCC SHOWCONTIG (tablename)

–Table with the index
DBCC SHOWCONTIG (tablename, indexname)

–Do not block anything (run during prod hours)
DBCC SHOWCONTIG (tablename, indexname) WITH FAST –(2005)