Friday, October 14, 2005

Determine Possible Row Size for SQL Server Table

How do I determine the row size of all tables in a particular DB?

"...

SELECT OBJECT_NAME (sc.[id]) tablename
, COUNT (1) nr_columns
, SUM (sc.length) maxrowlength
FROM syscolumns sc
join sysobjects so
on sc.[id] = so.[id]
WHERE so.xtype = 'U'
GROUP BY OBJECT_NAME (sc.[id])
ORDER BY SUM (sc.length) desc

..."


As I mentioned before I'm currently playing in Unicode land. I'm in the process of updating a number of SQL Server tables to support unicode/non-ascii text (i.e. varchar to nvarchar, etc).

Given than nvarchar's take twice as many bytes as varchar's, row length management is an important part of this conversion.

To help, I needed a quick way to get the existing possible row sizes for my tables. A quick google brought me the above SQL, which fit the bill perfectly...

And I'm sure I'll need it again, so in order to find it I'm posting it here.

1 comment:

Anonymous said...

Found very usefull!

Thanks you very much :-)