How to get a set of results from all tables in a database using their disk space

Question

Is there a T-SQL query that I can run against a database that will provide a list of all the tables in that database, as well as their current disk usage? I know that I can look at it in SSMS with the properties dialog, and I know how to use sp_spaceused sproc to see it one table at a time, but I would like to evaluate the disk usage for all my tables, it is ordered with the highest level of use disk space. We need to significantly reduce our database size, so I would like to see which tables are the most intruders.

Answer 1

create table #Temp ( name nvarchar(128), [rows] char(11), reserved varchar(18), data varchar(18), index_size varchar(18), unused varchar(18) ) insert into #Temp exec sp_msforeachtable 'sp_spaceused ''?''' select * from #Temp order by cast(replace(reserved,' kb','') as int) desc 

Answer 2

sys.allocation_units , see total_pages. Each rowset (index section) has 3 allocation units (DATA, SLOB and LOB), see Table and organization of indexes . Join sys.partitions to get object_id and index_id. Index_id 0 is a bunch of unordered tables, index id 1 is a clustered index. Each table (index) has at least one partition, if not partitioned:

 select object_name(p.object_id) as [name], object_schema_name(p.object_id) as [schema], i.name as [index], i.type_desc, au.type_desc, p.partition_number, p.rows, au.total_pages * 8 as [space (kb)] from sys.allocation_units au join sys.partitions p on au.container_id = p.partition_id join sys.indexes i on p.object_id = i. object_id and i.index_id = p.index_id order by [space (kb)] desc; 

Answer 3

Have you viewed the Disk Usage Summary Report ?

"To view the report, expand the" Management "folder, right-click" Data Collection ", select" Reports ", select" Management Data Warehouse "and click" Disk Usage Overview "

The Disk Usage Collection Kit report provides an overview of the disk space used for all databases in an instance of SQL Server, as well as data and log file growth trends for each of these databases.
- The pivot table displays the initial size (in megabytes) and the current size of all the databases installed on the server that the data collector controls.
- Information on trend and average growth is shown graphically and numerically for data files and logs.