A union of curiosity and data science

Knowledgebase and brain dump of a database engineer

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

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

Example use of sp_spaceused

exec sp_spaceused 'user_view_track'


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.


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

create procedure sp_get_database_table_sizes
table #table_sizes (
      , 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]

sp_MSforeachtable 'insert into #table_sizes exec sp_spaceused ''?'''

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


Add comment