Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2006
    Posts
    2

    Unanswered: Calculating the size of a table in a db

    If I have a database with a list of tables is there a way to calculate the size of each table individually and
    then calculate the size all the tables. If you have 1 table with say 10 rows and 3 columns and the width of the
    columns are of variable length you could do something like

    ( column1width + column2width + column3width ) * No.of Rows = Tablesize

    So my question is can I reference the column width of different columns in a
    table using sql ?

    Another issue is that some of the columns are different datatypes so I should be taking that
    into consideration as well.

    From searching the internet so far I have seen little on SQL showing how to
    reference column width in a table.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    read about sp_spaceused in Books online
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Yes sp_spaceused is the way to go else see Estimating the Size of a Table in Books online

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by ace333
    If you have 1 table with say 10 rows and 3 columns and the width of the
    columns are of variable length you could do something like

    ( column1width + column2width + column3width ) * No.of Rows = Tablesize
    In addition to the above comments (and it is clear if you read one of the links) this is wrong. For variable length columns the size if dependent on their contents.

    Quote Originally Posted by ace333
    So my question is can I reference the column width of different columns in a
    table using sql ?
    have a look at INFORMATION_SCHEMA.COLUMNS
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Aug 2006
    Posts
    2

    More info on sp_spaceused

    Ya sp_spaceused is good but is there a way I can get a breakdown of how much space is being taken up by each table in the database.

    So my Database object is called DBStore and it contains tables
    Table1
    Table2
    Table3 etc.... It would be great if I could see what stats on each table...

    This is the query that I have used myself but I'm not sure if it works because some of the tables that have 0 rows are saying that there is data in them.
    See what you guys think anyway ? Look forward to your feedback ?

    select a.name as "NAME",a.xtype as "XTYPE", sum(b.length) * count(*) as "TABLE SIZE"
    from sysobjects a, syscolumns b, systypes c
    where a.id=b.id -- Table ID = Column ID
    and b.xtype = c.xtype
    and a.name like '%'
    and a.xtype = 'U'
    group by a.name,a.xtype
    order by "TABLE SIZE";

  6. #6
    Join Date
    Sep 2006
    Posts
    11
    select the database then use view-> Taskpad in sql enterprise manager. Then it will list out the space occuoied by each table in your database.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •