August 10, 2007 at 5:58 pm
Hi,
We're runnning a vendor application that likes to create really wide columns in our database some exceeding 700 characters. I'm looking for a script that will tell me if the amount of data in a table is hitting the 8K barrier.
Thanks!
August 10, 2007 at 6:31 pm
Simply put, it either hits the barrier and stays valid or the insert errors out.
You can check this out as a very basic starting point : SELECT SUM(LEN(?not sure here?)) FROM dbo.SysColumns GROUP BY ID.
Anything above 8000 will begin to be dangerous!
August 13, 2007 at 7:46 am
Thanks. Ninja's. I'll give it a try.
August 13, 2007 at 8:03 am
If you're hitting it in a table, you'd need to check the data rows.
Go to BOL and get the data type sizes, (ints are 4 bytes, etc) and then you can calculate the fixed sizes. For varchar, you can use LEN() to get the actual size, so you can see which ones are > than some value. For text/image, you can cast them to a varchar, but they don't really impact row size.
August 13, 2007 at 8:31 am
That's why I was using the SysColumns approach, that way you can find potential problem tables. Once you have that list, you can start looking for rows that are close to that limit. But I would firstly see if and how I can take them back down under 8060 which is the best solution on the long run (assuming time permits to update the application).
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply