This is a comprehensive view for fragmentation, together with size in bytes (from vwTable). Keep in mind that when assessing fragmentation, we are concerned with larger tables in bytes, not necessarily larger tables in rows. This is because we are concerned with disk I/O. Although a tall skinny table with millions of rows may appear as a candidate to scrutinize, it is possible for a table with far fewer rows to incur more disk I/O if it is a wide table.
This view works well for instances that do not have too many databases (say under 20). If there are 50-100+ databases, this becomes slower. For those, I have another solution which will be posted next month.
IF OBJECT_ID('dbo.vwFragmentation') Is Not Null
DROP VIEW dbo.vwFragmentation
CREATE VIEW dbo.vwFragmentation
/* DATE AUTHOR REMARKS
12/17/19 PPaiva Initial creation.
SELECT TOP 1000 *
WHERE DB = 'MyDB'
AND TotalMB > 100
ORDER BY PercFrag desc
Convert(varchar(16), GetDate(), 120) InsertDate
FROM dbo.vwIndexPhysicalStats i
JOIN vwTable t
ON t.Instance = i.Instance
AND t.DB = i.DB
AND t.TableName = i.ObjName