use [databasename]
go
select d.name schema_name, b.name table_name,c.name index_name, c.allow_page_locks , page_count , avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (DB_ID(),null,null,null,null) as A
JOIN sys.objects AS B
ON A.object_id = B.object_id
JOIN sys.indexes AS C
ON A.object_id = C.object_id AND A.index_id = C.index_id
JOIN sys.schemas D
ON B.schema_id = D.schema_id
WHERE B.type = 'U'
and C.index_id > 0
ORDER BY A.avg_fragmentation_in_percent DESC