Update: I made a mistake in the previous script, it’s been fixed now. Thanks for Dana for pointing it out.
SqlServerCentral reader mikeg13 suggested using total spaced used on each table on my table space and row count post. So here comes version 2:
SCHEMA_NAME(o.schema_id) + ‘.’ + OBJECT_NAME(p.object_id) AS NAME,
SUM(reserved_page_count) * 8 AS total_space_used_kb,
SUM(CASE WHEN index_id < 2 THEN reserved_page_count ELSE 0 END) * 8 AS table_space_used_kb,
SUM(CASE WHEN index_id > 1 THEN reserved_page_count ELSE 0 END) * 8 AS nonclusteredd_index_spaced_used_kb,
MAX(row_count) AS row_count
sys.dm_db_partition_stats AS p INNER JOIN sys.all_objects AS o ON p.object_id = o.object_id
o.is_ms_shipped = 0
SCHEMA_NAME(o.schema_id) + ‘.’ + OBJECT_NAME(p.object_id)
ORDER BY NAME
Mikeg13, yes, what you said make sense and thank you for your suggestion.