Check the Rowsize of each Row

Today I show you a small script that outputs the rowsize for every row in a specified table.


This way you can check your rowsize and find extraordinary rowsizes in your tables.

Copyable Script:
/***** declares the variable for Tablename *****/
declare @t varchar(50)
/***** declares the variable for output ID *****/
declare @ID varchar(10)
/***** declares the variable for the command *****/
declare @sqlcmd varchar(max)

/***** sets variable for the table *****/
set @t = 'dbo.article'
/***** sets the variable for ID column in output *****/
set @ID = 'itemno'
/***** sets the variable for cmd *****/
set @sqlcmd =    'select ' + @ID +' , (0'
                select @sqlcmd = @sqlcmd + ' +case when isnull(len(' + name +
                '), 1) > isnull(datalength(' + name + '), 1) then isnull(len
                (' + name + '), 1) else isnull(datalength(' + name + '), 1) end '
                from syscolumns where id = object_id(@t)
set @sqlcmd =    @sqlcmd + ') as rowsize from ' + @t + ' (nolock) order by rowsize desc'
/**** Prints the execute cmd *****/
select @sqlcmd as Command
/**** Executes the cmd *****/
exec (@sqlcmd)