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.
Friday, October 14, 2005
1 comment:
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...
Found very usefull!
ReplyDeleteThanks you very much :-)