Table sizes

  • Hi everyone,

    Does anyone have a query that shows the table sizes (actual size on disk) of a database?  Thanks in advance for your help.

  • I think sp_spaceused will help you.

  • Thanks Antares, I was hoping that a query would be able to show me which tables are the top 10 tables based on the amount of space they occupy on disk.  sp_spaceused is inefficient for my purposes. 

  • SELECT TOP 10 used AS "# of Pages", rows AS "# of Rows", (used * 8) / 1024 AS "# of MB", object_name(id) AS TableName

    FROM sysindexes WHERE indid IN(1,2,255)

    order by used DESC

  • Hi,

    Actually sp_spaceused can be of somuse, like so:

    create table #table_size(

     name nvarchar(128),

     rows int,

     reserved_kb varchar(18),

     data_kb varchar(18),

     index_kb varchar(18),

     unused_kb varchar(18) )

    insert #table_size exec sp_MsForeachTable 'exec sp_spaceused ''?''--,true'

    -- "uncomment" --,true above to run updateusage

    select top 10

     name,

     rows,

     convert(int,replace(reserved_kb,' KB','')) as reserved_kb,

     convert(int,replace(data_kb,' KB','')) as data_kb,

     convert(int,replace(index_kb,' KB','')) as index_kb,

     convert(int,replace(unused_kb,' KB','')) as unused_kb

    from

     #table_size

    order by

     reserved_kb desc

    drop table #table_size

    /rockmoose


    You must unlearn what You have learnt

  • Great responses, thanks for your help.

  • I got this from someone online.  It puts it all into a table called DBATableAudit, but you can change the name to anything you want.

     

    declare @id int   

    declare @type character(2)   

    declare @pages int   

    declare @dbname sysname

    declare @dbsize dec(15,0)

    declare @bytesperpage dec(15,0)

    declare @pagesperMB  dec(15,0)

    create table #spt_space

    (

     objid  int null,

     rows  int null,

     reserved dec(15) null,

     data  dec(15) null,

     indexp  dec(15) null,

     unused  dec(15) null

    )

    set nocount on

    -- Create a cursor to loop through the user tables

    declare c_tables cursor for

    select id

    from sysobjects

    where xtype = 'U'

    open c_tables

    fetch next from c_tables

    into @id

    while @@fetch_status = 0

    begin

     /* Code from sp_spaceused */

     insert into #spt_space (objid, reserved)

      select objid = @id, sum(reserved)

       from sysindexes

        where indid in (0, 1, 255)

         and id = @id

     select @pages = sum(dpages)

       from sysindexes

        where indid < 2

         and id = @id

     select @pages = @pages + isnull(sum(used), 0)

      from sysindexes

       where indid = 255

        and id = @id

     update #spt_space

      set data = @pages

     where objid = @id

     /* index: sum(used) where indid in (0, 1, 255) - data */

     update #spt_space

      set indexp = (select sum(used)

        from sysindexes

        where indid in (0, 1, 255)

        and id = @id)

           - data

      where objid = @id

     /* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */

     update #spt_space

      set unused = reserved

        - (select sum(used)

         from sysindexes

          where indid in (0, 1, 255)

          and id = @id)

      where objid = @id

     update #spt_space

      set rows = i.rows

       from sysindexes i

        where i.indid < 2

        and i.id = @id

        and objid = @id

     fetch next from c_tables

     into @id

    end

    Drop Table DBATableSizeAudit

    select  TableName = (select left(name,60) from sysobjects where id = objid),

     Rows = convert(char(11), rows),

     ReservedKB = ltrim(str(reserved * d.low / 1024.,15,0)),

     DataKB = ltrim(str(data * d.low / 1024.,15,0)),

     IndexSizeKB = ltrim(str(indexp * d.low / 1024.,15,0)),

     UnusedKB = ltrim(str(unused * d.low / 1024.,15,0))

    into DBATableSizeAudit  

    from  #spt_space, master.dbo.spt_values d

    where  d.number = 1

    and  d.type = 'E'

    order by reserved desc

    drop table #spt_space

    close c_tables

    deallocate c_tables

     

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply