Calculating Maximum Row Size of a table

A quick script to calculate the maximum row size of a single row in a table.

MAX_LENGTH on the sys.columns table shows the maximum size in bytes
of the column. NB Strong Unicode (NVARCHAR) will take 2 bytes per character i.e.
will be twice the length of the column.

I use it here to show the size of the row.

Replace 'tablename' as appropriate.

SELECT
 SYS.OBJECTS.[NAME],
 SYS.OBJECTS.[OBJECT_ID],
 COUNT(SYS.COLUMNS.[NAME]) AS COLUMNCOUNT,
 SUM(SYS.COLUMNS.MAX_LENGTH) AS MAXLENGTH
FROM SYS.OBJECTS
INNER JOIN SYS.COLUMNS ON SYS.OBJECTS.OBJECT_ID = SYS.COLUMNS.OBJECT_ID
WHERE SYS.OBJECTS.[TYPE] = 'U'
--AND SYS.OBJECTS.[NAME] = 'tablename'
GROUP BY
 SYS.OBJECTS.[NAME],
 SYS.OBJECTS.[OBJECT_ID] 
ORDER BY 
 SYS.OBJECTS.[NAME] 

and the columns involved...

SELECT
  s.name AS schemaname
, o.name AS tablename
, c.name AS columnname
, c.max_length
FROM sys.objects o
INNER JOIN sys.columns c ON o.object_id = c.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id 
WHERE o.name  = 'tablename'