Finding table size (physical disk space)

zensunni

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

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

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

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

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

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

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)

re: Finding table size (physical disk space)

re: Finding table size (physical disk space)

re: Finding table size (physical disk space)

re: Finding table size (physical disk space)

re: Finding table size (physical disk space)

re: Finding table size (physical disk space)

Similar Microsoft SQL Server bytes

/bytes/development

/bytes/it

/bytes/about