Monday, April 11, 2016

Get each table space and their rows count

Sometime we need to know how much table space are used to store the data and also wish to know the number of rows stored in it, this query help you to get all the detail.


SELECT
 SCHEMA_NAME(o.schema_id) + ',' + OBJECT_NAME(p.object_id) AS name,
 reserved_page_count * 8 as space_used_kb,
 row_count
FROM sys.dm_db_partition_stats AS p
JOIN sys.all_objects AS o ON p.object_id = o.object_id
WHERE o.is_ms_shipped = 0
ORDER BY SCHEMA_NAME(o.schema_id) + ',' + OBJECT_NAME(p.object_id)

No comments:

Post a Comment