SQL Server – Calculate Row Size in a Table

Following Scripts will calculate rowsize for every table.

;with cte as (
SELECT TableName=a.name,SizeInBytes=sum(a.max_length)
FROM (
SELECT SysTab.NAME,SysCol.MAX_LENGTH
FROM SYS.COLUMNS SysCol inner join sys.tables SysTab on SysCol.object_id = SysTab.object_id
UNION ALL
SELECT SysTab.NAME,SysCol.MAX_LENGTH
FROM SYS.INDEXES SysInd INNER JOIN SYS.TABLES SysTab ON SysInd.OBJECT_ID = SysTab.OBJECT_ID
inner join sys.columns SysCol on SysCol.object_id = SysTab.object_id
inner join sys.index_columns IC on SysCol.object_id = IC.object_id
and SysCol.column_id = IC.column_id ) a
group by a.name

)
select TableName, SizeInBytes, case when SizeInBytes>8060 then 1 else 0 end SpillĀ  from CTE.

There is an additional column by name Spill in case the row size is exceeding 8060 bytes which is max a page can hold, anything beyond that will need that record to be accommodated in another page.

Refer to http://sqlnuts.com/2015/09/05/sql-server-understanding-data-density/ to know more about page.

Thanks.