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:
[code=mysql]
SELECT
`DATA_LENGTH`
FROM
`information_sc hema`.`TABLES`
WHERE
(
`TABLE_SCHEMA` = 'database name'
AND
`TABLE_NAME` = 'table name'
)
LIMIT 1;
[/code]

[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:


[CODE=sql]sp_spaceused sysobjects[/CODE]


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.

[CODE=sql]Declare @table varchar(50)
select @table = 'sysobjects'

select ltrim(str((sele ct sum(reserved)
from sysindexes
where indid in (0, 1, 255)
and id = object_id(@tabl e) )
* d.low / 1024.,15,0) +
' KB')
from master.dbo.spt_ values d
where d.number = object_id(@tabl e)
and d.type = 'E'[/CODE]

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