SQL Server Performance Forum – Threads Archive

Updating systemtables to change column-collations

Hi there. Is there any way to update the system tables directly, to alter the collations of the columns in the user db’s? I’ve tried the script below: UPDATE Syscolumns SET collation = ‘SQL_Latin1_General_CP1_CS_AS’ WHERE name = ‘<AddrCode>’
AND id = object_id(‘<Compliance>’) but, I get the following error message: Server: Msg 271, Level 16, State 1, Line 1
Column ‘collation’ cannot be modified because it is a computed column. Can you please help me! I need to do thousands of these, and most of them has constraints on, so my script I generated to do the ALTER TABLE…. ALTER COLUMN does not suffice. Thanks.

---

I also got this error when using the ALTER TABLE ALTER COLUMN command. The only way around it was to pull the data out, truncate, run the command and load the data back in. I also had to drop indexes and defaults on the columns I was altering. Essentially we are doing the same thing, you doing the update on the sys table and me using the supplied sys command. Try as a test, unloading the data and dropping any index/default to see if you get the result you need. Unfortunately this might be the only way to do the update. Patrick

---

After struggling a bit, I eventually figured it out. Found the column that COLLATION is computed from, COLLATIONID. Wrote myself this script to build up the UPDATES: SELECT ‘UPDATE SYSCOLUMNS SET COLLATIONID =’ + ‘ ‘ + ”’53256”’ +
‘ WHERE NAME = ”’ +
Column_name +
”’ ‘ + ‘AND COLLATIONID = ‘ + ”’872468488”’ +
‘ AND ID = ‘ +
”” + RTRIM(CONVERT(int,sysobjects.id)) + ”” +
char(10) + ‘go’
FROM INFORMATION_SCHEMA.COLUMNS,
sysobjects
WHERE COLLATION_NAME = ‘SQL_Latin1_General_CP1_CI_AS’ AND
name = table_name and xtype = ‘u’ –AND data_type NOT IN (‘text’, ‘ntext’)
ORDER BY TABLE_NAME

---

Hmmmm pretty good but be careful. From BOL: "You cannot alter the collation of a column that is currently referenced by:
A computed column.
An index.
Distribution statistics, either generated automatically or by the CREATE STATISTICS statement.
A CHECK constraint.
A FOREIGN KEY constraint. " This is why I did what I did just to be sure.

---

]]>