Table Space and row count – Version 2

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:

SELECT

    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 

FROM

    sys.dm_db_partition_stats  AS  INNER JOIN  sys.all_objects AS  ON  p.object_id = o.object_id
WHERE
o.is_ms_shipped = 0
GROUP BY
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.

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s