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:

  1. Found very usefull!

    Thanks you very much :-)

    ReplyDelete

NOTE: Anonymous Commenting has been turned off for a while... The comment spammers are just killing me...

ALL comments are moderated. I will review every comment before it will appear on the blog.

Your comment WILL NOT APPEAR UNTIL I approve it. This may take some hours...

I reserve, and will use, the right to not approve ANY comment for ANY reason. I will not usually, but if it's off topic, spam (or even close to spam-like), inflammatory, mean, etc, etc, well... then...

Please see my comment policy for more information if you are interested.

Thanks,
Greg

PS. I am proactively moderating comments. Your comment WILL NOT APPEAR UNTIL I approve it. This may take some hours...