GetSizeOfAllTables

drop procedure Usp_GetSizeOfAllTables
go
create procedure Usp_GetSizeOfAllTables
as
begin
	/* declare local cursor for getting the list of all tables */
	/* ------------------------------------------------------- */
	Declare 
		Lcur_Sysobjects 
	cursor for
		Select 
			name 
		from 
			sysobjects 
		where 
			type = 'U' 
		order by 
			name
	
	/* declare local table for storing the tablename,number of rows and rowlength */
	/* -------------------------------------------------------------------------- */
	create table #tblTableSize 	
	(
		TableName	sysname null,
		RowLength	dec(15) null
	)

	/* create local temp table */
	create table #tblspaceused
	(
		TableName	sysname,
		rows		varchar(25)	null,
		reserved	varchar(25)  	null,
		data		Varchar(25)  	null,
		indexp		varchar(25)  	null,
		unused		varchar(25)	null
	)


	/* declare local variables */
	/* ----------------------- */
	declare @lsTableName sysname
	declare @liCol int
	declare @liRowLength dec(15)
	declare @liColLength dec(15)
	declare @lsCommand varchar(255)
	
	/* open the local cursor */
	/* --------------------- */
	open Lcur_SysObjects
	
	/* fetch the first table name from the cursor */
	/* ------------------------------------------ */
	fetch next from
		Lcur_SysObjects
	into
		@lsTableName
	
	/* fetch the remaining table names from the cursor */
	/* ----------------------------------------------- */
	while (@@fetch_status = 0)
	begin
	
		/* initialize local variables */
		/* -------------------------- */
		set @liCol = 1
		set @liRowLength = 0
		set @liColLength = 0
	
	
		/* loop to get the row length */
		/* -------------------------- */
		while (1=1)
		begin
			select 	@liColLength = col_length(@lsTableName,col_name(object_id(@lsTablename),@liCol)) 
			if @liColLength > 0
				begin
					set @liCol = @liCol + 1
					set @liRowLength = @liRowLength + @liColLength
				end
			else
			begin
				break
			end
		end
	
		/* insert the values in the local table */
		/* ------------------------------------ */
		insert 
			#tblTableSize
		values
			(@lsTableName,@liRowLength)

		/* execute sp_spacedused to get the usage for each table */
		/* ----------------------------------------------------- */
		select @lsCommand = 'sp_spaceused "' + @lsTableName + '"'
		insert into #tblspaceused execute (@lsCommand)


		/* fetch the next table name from the local cursor */
		/* ----------------------------------------------- */
		fetch next from
			Lcur_SysObjects
		into
			@lsTableName
	end
	close Lcur_SysObjects
	deallocate Lcur_SysObjects

	select 
		a.TableName,
		a.Rows,
		B.RowLength 			as 'RowLengInBytes',
		replace(a.Reserved,'KB','') 	as 'ReservedSpaceInKB',
		replace(a.Data,'KB','') 	as 'UsedByDataInKB',
		replace(a.indexp,'KB','') 	as 'UsedByIndexInKB',
		replace(a.unused,'KB','') 	as 'UnUsedSpaceInKB'
	from 
		#tblspaceused a,
		#tblTableSize b
	where
		a.TableName = b.TableName 
	order by
		convert(int,rows) desc


end
-- Usp_GetSizeOfAllTables