Determine the SQL Server database table sizes

Products

• IT Management Suite

Issue/Introduction

 Is there a way to know the table sizes in the Symantec_CMDB database?

Environment

ITMS 8.x

Resolution

You can run the following query in the SQL Management Studio or create a report from it.

This query shows the following information for all tables larger than 5 KB,

• the table name
• the number of rows in the table
• the amount of disk spaced reserved for the table, in GB's
• the percent of the reserved space that is currently unused

 

--In SQL Server, the page size is 8 KB, or 128 pages per megabyte

select
    a2.name [Table],
    replace(convert (nvarchar, convert(money, a1.rows), 1), '.00','') [Row Count],
    cast (a1.reserved/128.0/1024 as decimal(9,3)) [Reserved GB],
    case when a1.reserved = 0 then 0
    else cast ((100-(100.0*a1.used/a1.reserved)) as decimal(9,2)) end [Unused %]
from (
    select
        ps.object_id,
        sum (cast (ps.row_count as bigint)) rows,
        sum (cast (ps.reserved_page_count as bigint)) reserved,
        sum (cast (ps.used_page_count as bigint)) used
    from sys.dm_db_partition_stats ps
    inner join sys.all_objects a2
        on a2.object_id = ps.object_id
        and a2.type IN ('U')
    group by ps.object_id
    )  a1

left join sys.all_objects a2 ON a1.object_id = a2.object_id
where (a1.reserved/128) > 5
ORDER BY a1.rows DESC, a2.name

 

Example results returned:

 

Another example for this query that you can use:

declare @tablesize table (name nvarchar(200), [rows] nvarchar(100), reserved nvarchar(100), data nvarchar(100), index_size nvarchar(100), unused nvarchar(100))
insert into @tablesize
exec sp_MSforeachtable "sp_spaceused '?'"

select name [Name], [rows] [Rows], 
    cast(replace(ts.reserved, ' KB','') as bigint) [Reserved KB],
    cast(replace(ts.data, ' KB','') as bigint) [Data KB],
    cast(replace(ts.index_size, ' KB','') as bigint) [Index Size KB],
    cast(replace(ts.unused, ' KB','') as bigint) [Unused KB]
from @tablesize ts
order by 3 desc


Example results from the above query: