How can I get the actual data size per row in a SQL Server table?

I found this script
sql-server-2005-reaching-table-row-size-limit
that seems to return the row size per defined data type lengths. I need a script that would give me all the rows in a table that their max data size is over the recommended 8024 (whatever MS recommends)

Try this script:

declare @table nvarchar(128)
declare @idcol nvarchar(128)
declare @sql nvarchar(max)

--initialize those two values
set @table = 'YourTable'
set @idcol = 'some id to recognize the row'

set @sql = 'select ' + @idcol +' , (0'

select @sql = @sql + ' + isnull(datalength(' + QUOTENAME(name) + '), 1)' 
        from  sys.columns 
        where object_id = object_id(@table)
        and   is_computed = 0
set @sql = @sql + ') as rowsize from ' + @table + ' order by rowsize desc'

PRINT @sql

exec (@sql)

The rows will be ordered by size, so you can check from top to down. The rowsize column is in bytes. See more info: DATALENGTH()

StackExchange Database Administrators Q#25531, answer score: 70