This is a great little piece of SQL which does something really useful. It displays the tables in a database together with the row counts for each, in descending order of size. This is useful if you are performing some database maintenance and want to know which tables are using the most space.

Note : is_ms_shipped = 0 is used so that system tables are not included.


SELECT objs.name,
part_stats.row_count
FROM sys.indexes AS indxs
INNER JOIN sys.dm_db_partition_stats AS part_stats ON indxs.OBJECT_ID = part_stats.OBJECT_ID
INNER JOIN sys.objects AS objs ON indxs.OBJECT_ID = objs.OBJECT_ID
AND indxs.index_id = part_stats.index_id
WHERE indxs.index_id < 2 AND objs.is_ms_shipped = 0 ORDER BY part_stats.row_count desc