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 
(
TableNamesysname null,
RowLengthdec(15) null
)

/* create local temp table */create table #tblspaceused
(
TableNamesysname,
rowsvarchar(25)null,
reservedvarchar(25)  null,
dataVarchar(25)  null,
indexpvarchar(25)  null,
unusedvarchar(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