Check defragmentation

Simple query to check the fragmentation of DB (I suggest to run in data and model too)

SELECT DB_NAME(PS.database_id) AS dbName,
S.name  AS SchemaName,
O.name AS TableName,
b.name,
ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID AND ps.index_id = b.index_id
INNER JOIN sys.objects O  ON PS.object_id = O.object_id
INNER JOIN sys.schemas S ON S.schema_id = O.schema_id
WHERE  ps.avg_fragmentation_in_percent >= 20 — Indexes having Fragmentation >=20
AND PS.index_type_desc IN (‘CLUSTERED INDEX’,’NONCLUSTERED INDEX’) — Only get clustered and nonclustered indexes
AND b.is_hypothetical = 0 — Only real indexes
AND O.type_desc = ‘USER_TABLE’ — Restrict to user tables
AND PS.page_count > 8 — ignore tables less tha 64K
ORDER BY ps.avg_fragmentation_in_percent DESC