Earch Table Size

  • Hi Every body,

    how can know for each table size i need

    sp_speace used only for one table

    i need all tables sizes

    sp_msforeachtable @comman1='exec sp_speaceused authors'

    it will giveeing some errors

    could any body plz help

    How can find out all tables size

    Really appriciate those guys

    Regards

    Shiv

  • I don't remember where I got this script, probably from SSC...

    Here you are:

    declare @idint

    declare @typecharacter(2)

    declare@pagesint

    declare @dbname sysname

    declare @dbsize dec(15,0)

    declare @bytesperpagedec(15,0)

    declare @pagesperMBdec(15,0)

    create table #spt_space

    (

    objidint null,

    rowsint null,

    reserveddec(15) null,

    datadec(15) null,

    indexpdec(15) null,

    unuseddec(15) null

    )

    set nocount on

    -- Create a cursor to loop through the user tables

    declare c_tables cursor for

    select object_id(table_name)

    frominformation_schema.tables

    wheretable_type = 'BASE TABLE'

    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 = cast(convert(char(11), rows) as bigint),

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

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

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

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

    from #spt_space, master.dbo.spt_values d

    where d.number = 1

    and d.type = 'E'

    order by rows desc

    drop table #spt_space

    close c_tables

    deallocate c_tables

    Regards

    Gianluca

    -- Gianluca Sartori

  • Heh... I know where this one came from because it avoids RBAR 😉

    --===== "Space Used on Steroids"

    -- If "UnusedKB" is negative, it's likely you need to run DBCC UpdateUsage on the table.

    -- If the RowModCtr is high (contains number of rows inserted/updated/deleted sinse last stats update)

    -- you might want to run UPDATE STATISICS on those tables.

    -- Jeff Moden

    SELECT DBName = DB_NAME(),

    Owner = USER_NAME(so.UID),

    TableName = so.Name,

    TableID = so.ID,

    MinRowSize = MIN(si.MinLen),

    MaxRowSize = MAX(si.XMaxLen),

    ReservedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB,

    DataKB = SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.DPages ELSE 0 END) * pkb.PageKB

    + SUM(CASE WHEN si.IndID IN ( 255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB,

    IndexKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB

    - SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.DPages ELSE 0 END) * pkb.PageKB

    - SUM(CASE WHEN si.IndID IN ( 255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB,

    UnusedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB

    - SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB,

    Rows = SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.Rows ELSE 0 END),

    RowModCtr = MIN(si.RowModCtr),

    HasTextImage = MAX(CASE WHEN si.IndID IN ( 255) THEN 1 ELSE 0 END),

    HasClustered = MAX(CASE WHEN si.IndID IN ( 1 ) THEN 1 ELSE 0 END)

    FROM dbo.SysObjects so,

    dbo.SysIndexes si,

    (--Derived table finds page size in KB according to system type

    SELECT Low/1024 AS PageKB --1024 is a binary Kilo-byte

    FROM Master.dbo.spt_Values

    WHERE Number = 1 --Identifies the primary row for the given type

    AND Type = 'E' --Identifies row for system type

    ) pkb

    WHERE si.ID = so.ID

    AND si.IndID IN (0, --Table w/o Text or Image Data

    1,

    255) --Table w/ Text or Image Data

    AND so.XType = 'U' --User Tables

    AND PERMISSIONS(so.ID) 0

    GROUP BY so.Name,

    so.UID,

    so.ID,

    pkb.PageKB

    ORDER BY so.Name

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks!

    Nice fast results 🙂

    Jeff Moden (7/17/2009)


    Heh... I know where this one came from because it avoids RBAR 😉

    --===== "Space Used on Steroids"

    -- If "UnusedKB" is negative, it's likely you need to run DBCC UpdateUsage on the table.

    -- If the RowModCtr is high (contains number of rows inserted/updated/deleted sinse last stats update)

    -- you might want to run UPDATE STATISICS on those tables.

    -- Jeff Moden

    SELECT DBName = DB_NAME(),

    Owner = USER_NAME(so.UID),

    TableName = so.Name,

    TableID = so.ID,

    MinRowSize = MIN(si.MinLen),

    MaxRowSize = MAX(si.XMaxLen),

    ReservedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB,

    DataKB = SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.DPages ELSE 0 END) * pkb.PageKB

    + SUM(CASE WHEN si.IndID IN ( 255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB,

    IndexKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB

    - SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.DPages ELSE 0 END) * pkb.PageKB

    - SUM(CASE WHEN si.IndID IN ( 255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB,

    UnusedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB

    - SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB,

    Rows = SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.Rows ELSE 0 END),

    RowModCtr = MIN(si.RowModCtr),

    HasTextImage = MAX(CASE WHEN si.IndID IN ( 255) THEN 1 ELSE 0 END),

    HasClustered = MAX(CASE WHEN si.IndID IN ( 1 ) THEN 1 ELSE 0 END)

    FROM dbo.SysObjects so,

    dbo.SysIndexes si,

    (--Derived table finds page size in KB according to system type

    SELECT Low/1024 AS PageKB --1024 is a binary Kilo-byte

    FROM Master.dbo.spt_Values

    WHERE Number = 1 --Identifies the primary row for the given type

    AND Type = 'E' --Identifies row for system type

    ) pkb

    WHERE si.ID = so.ID

    AND si.IndID IN (0, --Table w/o Text or Image Data

    1,

    255) --Table w/ Text or Image Data

    AND so.XType = 'U' --User Tables

    AND PERMISSIONS(so.ID) <> 0

    GROUP BY so.Name,

    so.UID,

    so.ID,

    pkb.PageKB

    ORDER BY so.Name

  • You bet... thanks for the feedback.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 4 (of 4 total)

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