Is there any way to find out the size / sizes of your database tables?

Question

Hi folks,

i've got around 10 tables in my sql 2008 server.

Currently, my mdf is around 3.5Gig. (I also have some binary data in some of the tables). So, I'm wondering if there's a way i could see which tables are the biggest in size.

Is this possible?

Maybe it's an index or FTS catalog instead?

Answer 1

 exec sp_spaceused <tablename>

Answer 2

exec sp_spaceused [tablename]

Answer 3

run this:

/******************************************************************************
**    File: “GetTableSpaceUseage.sql”
**    Name: Get Table Space Useage for a specific schema
**    Auth: Robert C. Cain
**    Date: 01/27/2008
**
**    Desc: Calls the sp_spaceused proc for each table in a schema and returns
**        the Table Name, Number of Rows, and space used for each table.
**
**    Called by:
**     n/a – As needed
**
**    Input Parameters:
**     In the code check the value of @schemaname, if you need it for a
**     schema other than dbo be sure to change it.
**
**    Output Parameters:
**     NA
*******************************************************************************/

/*—————————————————————————*/
/* Drop the temp table if it's there from a previous run                     */
/*—————————————————————————*/
if object_id(N'tempdb..[#TableSizes]') is not null
  drop table #TableSizes ;
go

/*—————————————————————————*/
/* Create the temp table                                                     */
/*—————————————————————————*/
create table #TableSizes
  (
    [Table Name] nvarchar(128)   /* Name of the table */
  , [Number of Rows] char(11)    /* Number of rows existing in the table. */
  , [Reserved Space] varchar(18) /* Reserved space for table. */
  , [Data Space] varchar(18)    /* Amount of space used by data in table. */
  , [Index Size] varchar(18)    /* Amount of space used by indexes in table. */
  , [Unused Space] varchar(18)   /* Amount of space reserved but not used. */
  ) ;
go

/*—————————————————————————*/
/* Load the temp table                                                        */
/*—————————————————————————*/
declare @schemaname varchar(256) ;
-- Make sure to set next line to the Schema name you want!
set @schemaname = 'dbo' ;

-- Create a cursor to cycle through the names of each table in the schema
declare curSchemaTable cursor
  for select sys.schemas.name + '.' + sys.objects.name
      from    sys.objects
      , sys.schemas
      where   object_id > 100
        and sys.schemas.name = @schemaname
        /* For a specific table uncomment next line and supply name */
        --and sys.objects.name = 'specific-table-name-here'    
        and type_desc = 'USER_TABLE'
        and sys.objects.schema_id = sys.schemas.schema_id ;

open curSchemaTable ;
declare @name varchar(256) ;  /* This holds the name of the current table*/

-- Now loop thru the cursor, calling the sp_spaceused for each table
fetch curSchemaTable into @name ;
while ( @@FETCH_STATUS = 0 )
  begin    
    insert into #TableSizes
      exec sp_spaceused @objname = @name ;       
    fetch curSchemaTable into @name ;   
  end

/* Important to both close and deallocate! */
close curSchemaTable ;     
deallocate curSchemaTable ;


/*—————————————————————————*/
/* Feed the results back                                                     */
/*—————————————————————————*/
select [Table Name]
      , [Number of Rows]
      , [Reserved Space]
      , [Data Space]
      , [Index Size]
      , [Unused Space]
from    [#TableSizes]
order by [Table Name] ;

/*—————————————————————————*/
/* Remove the temp table                                                     */
/*—————————————————————————*/
drop table #TableSizes ;

taken from Robert Caine blog

Edited the code to parse, several chars that were in single quote used a special single quote, as well the -- sign.

This code is for Microsoft SQL 2005+

Answer 4

Sometimes I run this... It gets all tables to temp table, loops it through and gets sizes for all tables. Result data is in @tablesizes, so you can query it how you like.

Works in Sql Server >2005

declare @tables TABLE
(
    table_name nvarchar(200)
)

declare @tablesizes TABLE
(
    [name] nvarchar(200),
    [rows] int,
    reserved nvarchar(200),
    data nvarchar(200),
    index_size nvarchar(200),
    unused nvarchar(200),
    reserved_int int,
    data_int int,
    index_size_int int,
    unused_int int
)

declare @t nvarchar(200)

insert into @tables
select Table_Name from information_schema.tables

while exists(select * from @tables)
begin
    set @t=(select top 1 table_name from @tables)

    insert into @tablesizes([name],[rows],reserved,data,index_size,unused)
    exec sp_spaceused @t

    delete top (1) from @tables
end

update @tablesizes set 
reserved_int=convert(int, replace(reserved,' KB','')),
data_int=convert(int, replace(data,' KB','')),
index_size_int=convert(int, replace(index_size,' KB','')),
unused_int=convert(int, replace(unused,' KB',''))

select * from @tablesizes order by data_int desc

Answer 5

sys.allocations_units has the information you need. You join with sys.partitions to group all allocation units of a partition together and also to obtain the more usable object_id rather than the esoteric allocation_unit_id.

select object_name(p.object_id),
    sum(au.total_pages)*8 as [space_in_kb]
    from sys.partitions p
    join sys.allocation_units au on p.hobt_id = au.container_id
    group by p.object_id
    order by [space_in_kb]  desc

And yes, all tables (heap or clustered) are 'partitions', the terms does not refer to partitioned tables. sys.partitions also has the 'rows' column that may interest you.

Answer 6

This query shows the size of each table in the current database.

SELECT sysobjects.[name] AS [TableName],
    SUM(sysindexes.reserved) * 8 AS [Size(KB)],
    SUM(sysindexes.dpages) * 8 AS [Data(KB)],
    (SUM(sysindexes.used) - SUM(sysindexes.dpages)) * 8 AS [Indexes(KB)],
    (SUM(sysindexes.reserved) - SUM(sysindexes.dpages)) * 8 AS [Unused(KB)]
FROM dbo.sysindexes AS sysindexes
    JOIN dbo.sysobjects AS sysobjects ON sysobjects.id = sysindexes.id
WHERE sysobjects.[type] = 'U'
GROUP BY sysobjects.[name]
ORDER BY [Size(KB)] DESC