What is the easiest way to get statistics on disk usage for my SQL Server database?

Question

I have a SQL Server database with multiple hundreds of data tables in it, and I am looking for disk usage statistics, as it relates to each individual table.

Is there a quick way to find this information?

Answer 1

Marc Brown put together a stored procedure using the sp_spaceused and sp_tables system stored procedures to gather that information. Also, if you don't mind using a Microsoft private procedure, this stored procedure is a bit shorter method of accomplishing the same goal.

Answer 2

Which SQL Server version?
Which 'usage' statistics? Size? Reads? Writes?

For size you look into sys.allocation_units:

select object_name(object_id) as [Object]
 , sum(au.total_pages)*8 as [Size (Kb)]
 from sys.allocation_units au
 join sys.partitions p on au.container_id = p.partition_id
 group by object_id
 having sum(au.total_pages) > 0

For IO you look into sys.dm_db_index_usage_stats.

Another possible approach is to enable performance collector sets and use the SQL Server 2008 Management Data Warehouse.