SELECT
TableID=(SELECT distinct so.name FROM sys.objects so INNER JOIN sys.indexes ON so.object_id = si.object_id)
,si.name as Indexname ,
convert(decimal(9,5),round(ps.avg_fragmentation_in_percent,5)) as Fragmentation,
partition_number AS partitionnum,
ps.OBJECT_ID as objectID,
ps.index_id AS indexid,
0 as NewFragmentation
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes si ON ps.OBJECT_ID = si.OBJECT_ID
AND ps.index_id = si.index_id
WHERE(ps.database_id = DB_ID() And si.name Is Not null)
AND avg_fragmentation_in_percent >= 30
and ps.index_id<> 0 ORDER BY TableID,Indexname
