Table size

Discussion in 'H-Sphere Shared Hosting' started by rmethod, Jan 18, 2006.

  1. rmethod

    rmethod Perch

    Hi all,
    Just a quick FYI for you. Here is a T-SQL script that you can run to see each table size in your database.

    BEGIN SCRIPT
    ==========================================

    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

    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) + ' ' + 'KB'),
    DataKB = ltrim(str(data * d.low / 1024.,15,0) + ' ' + 'KB'),
    IndexSizeKB = ltrim(str(indexp * d.low / 1024.,15,0) + ' ' + 'KB'),
    UnusedKB = ltrim(str(unused * d.low / 1024.,15,0) + ' ' + 'KB')

    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

Share This Page

JodoHost - 26,000 hosting end-users in 100 countries
Plesk Web Hosting
VPS Hosting
H-Sphere Web Hosting
Other Services