Thread: Calculating the size of a table in a db

1. Registered User
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. Annie's Dog Walker
Join Date
Nov 2004
Location
on the wrong server
Posts
8,842

3. Registered User
Join Date
May 2005
Location
South Africa
Posts
1,369
Yes sp_spaceused is the way to go else see Estimating the Size of a Table in Books online

4. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
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.

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

5. Registered User
Join Date
Aug 2006
Posts
2

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. Registered User
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
•