Connecting Tech Pros Worldwide Help | Site Map

Finding table size (physical disk space)

  #1  
Old October 4th, 2007, 10:53 PM
Member
 
Join Date: May 2007
Posts: 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.
  #2  
Old October 4th, 2007, 11:14 PM
pbmods's Avatar
Site Moderator
 
Join Date: Apr 2007
Location: Texas
Posts: 5,435
Provided Answers: 1

re: Finding table size (physical disk space)


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.  
  #3  
Old October 5th, 2007, 05:18 AM
Member
 
Join Date: May 2007
Posts: 101

re: Finding table size (physical disk space)


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.
  #4  
Old October 5th, 2007, 01:11 PM
pbmods's Avatar
Site Moderator
 
Join Date: Apr 2007
Location: Texas
Posts: 5,435
Provided Answers: 1

re: Finding table size (physical disk space)


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.
  #5  
Old October 11th, 2007, 11:02 PM
Member
 
Join Date: May 2007
Posts: 101

re: Finding table size (physical disk space)


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.
  #6  
Old October 12th, 2007, 01:22 AM
iburyak's Avatar
Expert
 
Join Date: Nov 2006
Posts: 1,017

re: Finding table size (physical disk space)


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.

Last edited by pbmods; October 12th, 2007 at 01:54 AM. Reason: Changed [CODE] to [CODE=sql].
  #7  
Old October 30th, 2007, 10:20 PM
Member
 
Join Date: May 2007
Posts: 101

re: Finding table size (physical disk space)


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>'
Reply


Similar Threads
Thread Thread Starter Forum Replies Last Post
Finding table size (physical disk space) zensunni answers 0 October 5th, 2007 05:14 AM