List table space and row count

Option 1: Use catelog views:

 
 

;WITH SpaceUsed AS

(

SELECT

    SCHEMA_NAME(o.schema_id) + ‘.’ + OBJECT_NAME(p.object_id) as name

    , p.rows

    , p.index_id

    , au.type_desc AS data_type

    , au.total_pages * 8 AS space_kb

    , o.type_desc AS object_type

FROM

    sys.partitions AS p join sys.allocation_units AS au ON p.partition_id = au.container_id

    join sys.all_objects AS o ON p.object_id = o.object_id

WHERE

    o.is_ms_shipped <> 1

)

SELECT

    name,

    [rows],

    SUM(space_kb) AS total_space_used_kb

FROM

    SpaceUsed

GROUP BY

    name,

    [rows]

ORDER BY

    name

 
 

Option 2: Use DMV:

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)

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