To generate a list of tables and the space each one uses to allow me to find areas for archiving, I use the following procedure :-
/*****************************************************************
Procedure to list table space used by each table in a database
ordered by reserved space.
Usage : exec s_SpaceUsed ‘<database name>’
LL 26 Feb 2010
*****************************************************************/
if exists (select * from sysobjects where id = object_id(N'[dbo].[s_SpaceUsed]’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)
drop procedure [dbo].[s_SpaceUsed]
GO
Create procedure [dbo].[s_SpaceUsed]
@SourceDB varchar(128)
as
set nocount on
declare @sql varchar(128)
create table #tables(name varchar(128))
select @sql = ‘insert #tables select TABLE_SCHEMA+”.”+TABLE_NAME from ‘ + @SourceDB + ‘.INFORMATION_SCHEMA.TABLES where TABLE_TYPE = ”BASE TABLE”’
exec (@sql)
create table #SpaceUsed (name varchar(128), rows varchar(11), reserved varchar(18), data varchar(18), index_size varchar(18), unused varchar(18))
create table #SpaceUsed2 (name varchar(128), rows numeric(11), reserved numeric(18), data numeric(18), index_size numeric(18), unused numeric(18))
declare @name varchar(128)
select @name = ”
while exists (select * from #tables where name > @name)
begin
select @name = min(name) from #tables where name > @name
select @sql = ‘exec ‘ + @SourceDB + ‘..sp_executesql N”insert #SpaceUsed exec sp_spaceused [‘ + @name + ‘]”’
exec (@sql)
end
insert into #SpaceUsed2
select name,replace(rows,’ KB’,”),replace(reserved,’ KB’,”),replace(data,’ KB’,”),
replace(index_size,’ KB’,”),replace(unused,’ KB’,”) from #SpaceUsed
select t.table_schema+’.’+t.table_name,s.rows,s.reserved,s.data,s.index_size,s.unused from #SpaceUsed2 s
inner join information_schema.tables t on t.table_name=s.name COLLATE database_default
where t.table_catalog=@SourceDB
order by reserved desc
drop table #tables
drop table #SpaceUsed
drop table #SpaceUsed2
go
exec [dbo].[s_SpaceUsed] ‘<database name>‘
go
Download Script Here