Return average row sizes for all tables

— List all tables in a database
— and order by size

dbcc updateusage (0) — Current DB
go

create table Z_TableSize (
TableName varchar(128) not null,
CountOfRows int not null,
Reserved varchar(200) not null,
Data varchar(200) not null,
Index_Size varchar(200) not null,
Unused varchar(200) not null
)
go

set nocount on

declare Diff_Cursor cursor
   for

select [Name] AS TableName
from dbo.sysobjects
where XType = ‘U’ and
 [Name] not in (‘dtProperties’, ‘Z_TableSize’)

declare @TableName nvarchar(128)

open Diff_Cursor

fetch next from Diff_Cursor into @TableName

while @@FETCH_STATUS = 0

begin
 
 insert Z_TableSize exec sp_spaceused @TableName

 fetch next from Diff_Cursor into @TableName
end

close Diff_Cursor

deallocate Diff_Cursor

select TableName, (Data2 / CountOfRows) as AverageRowBytes
from (
select TableName, Data,cast((replace(Data, ‘ KB’, ”)) as int) * 1000 as Data2, CountOfRows
from Z_TableSize
where CountOfRows > 0
) as t1
order by (Data2 / CountOfRows) desc

drop table Z_TableSize

go

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: