423,682 Members | 1,622 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,682 IT Pros & Developers. It's quick & easy.

Finding table size (physical disk space)

100+
P: 101
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
Share this Question
Share on Google+
6 Replies


pbmods
Expert 5K+
P: 5,821
Heya, Zensunni.

Check this out:
Expand|Select|Wrap|Line Numbers
  1. SELECT
  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;
  12.  
Oct 4 '07 #2

100+
P: 101
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

pbmods
Expert 5K+
P: 5,821
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

100+
P: 101
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

iburyak
Expert 100+
P: 1,017
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'
  3.  
  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

100+
P: 101
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.