469,281 Members | 2,486 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,281 developers. It's quick & easy.

Finding table size (physical disk space)

101 100+
How do I find how much space a table takes up on a hard drive? Is there any call you can make that returns a size?

If so, how can I display this with asp?

Thanks for any help and insights.
Oct 4 '07 #1
6 23176
5,821 Expert 4TB
Heya, Zensunni.

Check this out:
Expand|Select|Wrap|Line Numbers
  2.         `DATA_LENGTH`
  3.     FROM
  4.         `information_schema`.`TABLES`
  5.     WHERE
  6.     (
  7.             `TABLE_SCHEMA` = 'database name'
  8.         AND
  9.             `TABLE_NAME` = 'table name'
  10.     )
  11.     LIMIT 1;
Oct 4 '07 #2
101 100+
Ops, I'm retarded.

I meant to post in the MS SQL forum. Thanks for the reply though. MySQL is my DB of choice. Just wish all my clients could think that too :)

Thanks again and sorry.
Oct 5 '07 #3
5,821 Expert 4TB
Heya, zensunni.

No problem.

I'm going to go ahead and move this thread to the MS SQL forum, where our resident Experts will be better able to help you out.
Oct 5 '07 #4
101 100+
I'm pretty sure it's one of the system tables I have to access, which holds the information, but I'm unsure of which one.
Oct 11 '07 #5
1,017 Expert 512MB
Let's assume you want to find out space that is taken by sysobjects.
Usually to see space used by the table you execute following statement:

Expand|Select|Wrap|Line Numbers
  1. sp_spaceused sysobjects

Because you need to query the same data I assume you are interested in reserved space by this table in a database. Use query below to select the same data directly from system tables. If you use SQL 2005 add schema name before system table name.

Expand|Select|Wrap|Line Numbers
  1. Declare @table varchar(50)
  2. select @table = 'sysobjects'
  4. select ltrim(str((select sum(reserved)  
  5.                   from sysindexes
  6.                   where indid in (0, 1, 255)  
  7.                   and id = object_id(@table) ) 
  8.                 * d.low / 1024.,15,0) +  
  9.        ' KB')
  10.  from master.dbo.spt_values d  
  11.   where d.number = object_id(@table) 
  12.    and d.type = 'E'
Good Luck.
Oct 12 '07 #6
101 100+
Thanks all. I used these commands:

To find all the tables:
SELECT * FROM sysobjects WHERE type = 'U'

To find out how much space was used by a table:
exec sp_spaceused '<tablename>'
Oct 30 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Dan Muey | last post: by
19 posts views Thread by dchow | last post: by
36 posts views Thread by toedipper | last post: by
4 posts views Thread by chettiar | last post: by
6 posts views Thread by Maxi | last post: by
1 post views Thread by Nils Rennebarth | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.