A union of curiosity and data science

Knowledgebase and brain dump of a database engineer


Report your database table sizes. (2k, 2k5, 2k8)

31 January 2011 Don-Rickman SQL Server - Admin (0)
 
 
I needed a report to give me table growth by day for all of the tables in a given database.
 
I decided to start my table stats gathering by using the "sp_spaceused" stored procedure which gives data,index and unused space allocation for a given table. I combined this procedure with the undocumented sp_MSforEachTable statement to give me each owner.table combo in the database. I've used the below code as a base for my reporting. It's been added on for my needs but you can use the base for anything you like. Have fun.
 
Example use of sp_MSforEachTable

--The question mark in the expression below
--returns the two part qualified name [<owner/schema>].[object_name]

 
--for every table in the current database

exec sp_MSforeachtable 'print ''?'''

Result:
[dbo].[user]
[dbo].[users_roles]
[sales].[order]
[sales].[order_items]
 
 
 
Example use of sp_spaceused

exec sp_spaceused 'user_view_track'

Result:


 
 
The code below creates the temp table to hold all of the data for each table in my database.
I order it by the object total  space used in descending order.
 
#Table_Sizes

 

--set master and sp.. call from anywhere.
use master
go

create procedure sp_get_database_table_sizes
as
create table #table_sizes (
      [name]varchar(256)
      , rows bigint
      , reserved varchar(32)
      , data varchar(32)
      , index_size varchar(32)
      , unused varchar(32)
)

--The question mark in the expression below
--returns the two part qualified name [<owner/schema>].[object_name]
exec sp_MSforeachtable 'insert into #table_sizes exec sp_spaceused ''?'''

select *, getdate() [created], db_name() [database], @@servername [server] from #table_sizes
order by convert(int, replace(reserved, ' KB', '')) desc




Result: