Finding table size (physical disk space)

zensunni · #1 October 4th, 2007, 09:53 PM

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.

pbmods · #2 October 4th, 2007, 10:14 PM

Heya, Zensunni.

Check this out:

Expand|Select|Wrap|Line Numbers

 
SELECT

        `DATA_LENGTH`

    FROM

        `information_schema`.`TABLES`

    WHERE

    (

            `TABLE_SCHEMA` = 'database name'

        AND

            `TABLE_NAME` = 'table name'

    )

    LIMIT 1;

zensunni · #3 October 5th, 2007, 04:18 AM

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.

pbmods · #4 October 5th, 2007, 12:11 PM

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.

zensunni · #5 October 11th, 2007, 10:02 PM

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.

iburyak · #6 October 12th, 2007, 12:22 AM

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

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

 Declare @table varchar(50)

select @table = 'sysobjects'
 
select ltrim(str((select sum(reserved)  

                  from sysindexes

                  where indid in (0, 1, 255)  

                  and id = object_id(@table) ) 

                * d.low / 1024.,15,0) +  

       ' KB')

 from master.dbo.spt_values d  

  where d.number = object_id(@table) 

   and d.type = 'E'

Good Luck.

zensunni · #7 October 30th, 2007, 09:20 PM

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

Finding table size (physical disk space)

Popular Articles

What is Bytes?