Average length of all rows in a table

Average length of all rows in a table

Sometimes you want to know how much space the average row is in your table
Instead of writing tedious AVG(LEN(field1) + LEN(field2) + LEN(field3)) from tableA
You could use the INFORMATION_SCHEMA.COLUMNS system view and create a stored procedure that loops through the table, creates the script and executes/prints this script

Well lucky for you I have created this stored procedure and the code is posted below
Before anyone says what if you just run
dbcc showcontig (‘TableName’) with tableresults you will get a field AverageRecordSize use that instead, well that’s fine but what if I only want 3 out of 4 fields? This stored procedure will let me do that; I will run it with 0 instead of 1 for the last parameter.
Once the procedure runs I copy the SQL statement from the messages tab in Query Analyzer take out the fields I don’t need and we are done

This is how you run it
@chvTableName this is your table name
@bitExecutionType 1= select,0=print


exec AverageColumnLength 'authors',1 --select
exec AverageColumnLength 'authors',0 --print

--------------------------------------------------
create procedure dbo.AverageColumnLength
@chvTableName varchar(255),
@bitExecutionType bit
as
set nocount on

create table #TempColumnNames (
tID int identity not null,ColumnName varchar(50) not null)

insert into #TempColumnNames
select column_name
from INFORMATION_SCHEMA.COLUMNS
where table_name = @chvTableName

declare @chvSQLString varchar(2000)
select @chvSQLString = 'SELECT AVG('

declare @chvColumnName varchar(50)

declare @intLoopID int,@intLoopMaxID int
select @intLoopID = 1, @intLoopMaxID =Max(tID) from #TempColumnNames

while @intLoopID <= @intLoopMaxID
begin
select @chvColumnName =ColumnName from #TempColumnNames where tID =@intLoopID
select @chvSQLString =@chvSQLString + 'LEN(' + @chvColumnName + ')+' + char(10)
select @intLoopID = @intLoopID + 1
end


select @chvSQLString =left(@chvSQLString,len(@chvSQLString) -2) + ') ' + char(10)
select @chvSQLString =@chvSQLString + ' AS AverageColumnLength FROM ' + @chvTableName

if @bitExecutionType =0
print @chvSQLString
else
exec( @chvSQLString)--


drop table #TempColumnNames

set nocount off