Table size in DB

  • Does anyone know how to get specific table size and the ratio percent or actual MB of all tables in a particular DB?

  • did you try sp_spaceused ? (check bol)

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • No I didn't thanks this seems to give me what I need.

  • create table #spacedetails (name varchar(100),totrows bigint,reserved varchar(30), data varchar(30),index_size varchar(30), unused varchar(30))

    insert into #spacedetails

    exec sp_MSForeachtable @command1 = "sp_spaceused '?'"

    select name,totrows, replace(data,'KB','') as 'Data in KB',

    replace(Index_Size,'KB','') as 'Index Size in KB'  from #spacedetails order by name

    drop table #spacedetails

    You can use this if you want.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • You can get your required information using following pretty simple steps:

    1.                  Open enterprise manger

    2.                  Select the required database

    3.                  Select TaskPad option from view menu

    4.                  And view the detail table info (from TableInfo tab)

     

    Thanks,

    Irfan Baig.

    irfibaig@hotmail.com

     

  •  

    Try this script.  I believe I found it on this site a while ago and works great.

    BTW this is a GREAT site!

     

    SET NOCOUNT ON

    CREATE TABLE #TBLSize

     (Tblname varchar(80),

     TblRows int,

     TblReserved varchar(80),

     TblData varchar(80),

     TblIndex_Size varchar(80),

     TblUnused varchar(80))

    DECLARE @DBname varchar(80)

    DECLARE @tablename varchar(80)

    SELECT @DBname = DB_NAME(DB_ID())

    PRINT 'User Table size Report for (Server / Database):   ' + @@ServerName + ' / ' + @DBName

    PRINT ''

    PRINT 'By Size Descending'

    DECLARE TblName_cursor CURSOR FOR

    SELECT NAME

    FROM sysobjects

    WHERE xType = 'U'

    OPEN TblName_cursor

    FETCH NEXT FROM TblName_cursor

    INTO @tablename

    WHILE @@FETCH_STATUS = 0

    BEGIN

       INSERT INTO #tblSize(Tblname, TblRows, TblReserved, TblData, TblIndex_Size, TblUnused)

       EXEC Sp_SpaceUsed @tablename

         

       -- Get the next author.

       FETCH NEXT FROM TblName_cursor

       INTO @tablename

    END

    CLOSE TblName_cursor

    DEALLOCATE TblName_cursor

    SELECT  CAST(Tblname as Varchar(30)) 'Table',

     CAST(TblRows as Varchar(14)) 'Row Count',

     CAST(LEFT(TblReserved, CHARINDEX(' KB', TblReserved)) as int) 'Total Space (KB)',

            CAST(TblData as Varchar(14)) 'Data Space',

     CAST(TblIndex_Size  as Varchar(14)) 'Index Space',

            CAST(TblUnused as Varchar(14)) 'Unused Space'

    FROM #tblSize

    Order by 'Total Space (KB)' Desc

    PRINT ''

    PRINT 'By Table Name Alphabetical'

    /*SELECT  CAST(Tblname as Varchar(30)) 'Table',

     CAST(TblRows as Varchar(14)) 'Row Count',

     CAST(LEFT(TblReserved, CHARINDEX(' KB', TblReserved)) as int) 'Total Space (KB)',

            CAST(TblData as Varchar(14)) 'Data Space',

     CAST(TblIndex_Size  as Varchar(14)) 'Index Space',

            CAST(TblUnused as Varchar(14)) 'Unused Space'

    FROM #tblSize

    Order by 'Table'

    */

    DROP TABLE #TblSize

  • This script should do what you want.

    Script to analyze table space usage

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61762

     

     

  • Browse the scripts section of this site, there's several that might do the trick for you, or at least give you the framework to write something that does exactly what you want.

    -----
    [font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

  • Ack!  A cursor?

    --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)

  • Please post your non-cursor solution.

     

  • Look at the code for sp_spaceused to get the exact.  But here is a quick, and fairly accurate way.

    SELECT so.Name, SUM(dpages)*8/1024. pagesMB, SUM(Reserved)*8/1024. reservedMB

    FROM sysobjects so, sysindexes si

    where so.type='u'

    and so.id=si.id

    group by so.name

    order by so.name

    Brian

  • O-ki-do-ki

    DBCC UPDATEUSAGE(0)
    --===== "Space Used on Sterioids"
         -- Created by Jeff Moden
         -- 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.
     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 ReservedKB DESC

    --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)

  • This script that i have posted here is a non cursor script from the user side. But intern it calls a server side cursor but it executes faster than your user cursor. and gives details wanted.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • >>but it executes faster than your user cursor

    Who's "user cursor" solution are you talking about, Sugesh?

    --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)

  • Jeff, I am speaking about the one posted by Charlie.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

Viewing 15 posts - 1 through 15 (of 23 total)

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