Determine row size for table

Question

How to I determine the maximum row size for a table? I'm looking for a tool or script that does this so I don't have to add up each column's size by hand.

My goal is to produce a report of tables that are too wide so we can look into restructing them. I know we have several that are so wide that only 1 row fits on each 8K page, but I want to find the rest.

Answer 1

Have a look at INFORMATION_SCHEMA.COLUMNS. You can add the size of each column in a table to give you an idea which tables have the potential to overshoot the 8k/row rule.

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

You will figure out the rest when you see the query result.

Answer 2

Another way, run this then look at MaximumRecordsize

dbcc showcontig ('YourTableNameHere') with tableresults

Answer 3

Here is another query I got, but like Tomalak's it is broken because it doesn't take into account things like the overhead for variable length columns.

SELECT OBJECT_NAME (id) tablename
     , COUNT (1)        nr_columns
     , SUM (length)     maxrowlength
FROM   syscolumns
GROUP BY OBJECT_NAME (id)
ORDER BY OBJECT_NAME (id)