use master
go
set quoted_identifier on
go
if object_id('sp_rpttblspc') is not null
	drop procedure sp_rpttblspc 
go
create procedure sp_rpttblspc (@dbname varchar(30) = null, @tblname varchar(31) = null)
as
/********************************************************************************/
/*	Created By :	Umachandar Jayachandran	(UC)				*/
/*	Created On :	15 April 1996						*/
/*	Description:	Used to create a report of tables & number of rows.	*/
/********************************************************************************/
/*	Resources  :	http://www.umachandar.com/resources.htm         	*/
/********************************************************************************/
set nocount on
declare @tablename varchar(30), @cmdstr varchar(255)
select @dbname = isnull(@dbname, db_name()), @tblname = isnull(@tblname , '') + '%'
if db_id(@dbname) is null
	begin
		raiserror('Invalid database name was specified.', -1, -1)
		return(1)
	end
create table #tblspc
(name varchar(30), rows varchar(10), reserved varchar(20), data  varchar(20),
 index_size varchar(20), unused varchar(20))
exec('declare tbls cursor for select name from "' + @dbname + 
	'"..sysobjects where type = ''U'' and name like ''' + @tblname + '''')
open tbls
while('FETCH IS OK' = 'FETCH IS OK')
begin
	fetch next from tbls into @tablename
	if @@fetch_status < 0 break
	select @cmdstr = 'use "' + @dbname + '" exec sp_spaceused ''' + @tablename + ''''
	insert into #tblspc exec(@cmdstr)
	if @@error <> 0
		begin
			deallocate tbls
			raiserror('Fatal error, unable to obtain space details for tables.', -1, -1)
			return(1)
		end
end
deallocate tbls
select name as "Table Name:", rows as "Number Of Rows:" ,
	data as "Data Size", index_size as "Index Size",
	reserved as "Allocated Size"
from #tblspc
order by convert(int, rows) desc, 1
return(0)
go