select iu.database_id,
iu.object_id, OBJECT_NAME(iu.object_id) as TableName,
iu.index_id, x.name as IndexName, x.type_desc,
ips.avg_fragmentation_in_percent,
iu.user_scans, iu.system_scans, iu.user_updates
from sys.dm_db_index_usage_stats iu
INNER JOIN sys.partitions p ON iu.object_id = p.object_id and iu.index_id = p.index_id
INNER JOIN sys.objects o (nolock) ON iu.object_id = o.object_id and o.type='U'
INNER JOIN sys.indexes x (nolock) ON x.object_id = iu.object_id AND x.index_id = iu.index_id
and x.type_desc in ('CLUSTERED','NONCLUSTERED')
cross apply sys.dm_db_index_physical_stats (iu.database_id,iu.object_id,iu.index_id,p.partition_number,null) ips
WHERE iu.database_id = db_id('DATABASE_NAME') ----- CHANGE HERE
order by avg_fragmentation_in_percent desc
GO
iu.object_id, OBJECT_NAME(iu.object_id) as TableName,
iu.index_id, x.name as IndexName, x.type_desc,
ips.avg_fragmentation_in_percent,
iu.user_scans, iu.system_scans, iu.user_updates
from sys.dm_db_index_usage_stats iu
INNER JOIN sys.partitions p ON iu.object_id = p.object_id and iu.index_id = p.index_id
INNER JOIN sys.objects o (nolock) ON iu.object_id = o.object_id and o.type='U'
INNER JOIN sys.indexes x (nolock) ON x.object_id = iu.object_id AND x.index_id = iu.index_id
and x.type_desc in ('CLUSTERED','NONCLUSTERED')
cross apply sys.dm_db_index_physical_stats (iu.database_id,iu.object_id,iu.index_id,p.partition_number,null) ips
WHERE iu.database_id = db_id('DATABASE_NAME') ----- CHANGE HERE
order by avg_fragmentation_in_percent desc
GO
