Today, we interrupt our countdown series in progress to bring you some actual T-SQL coding.  I had to bring out this snippet and dust it off for my purposes, to change some column-level collation in a database.  To give you some background, we have a vendor product that requires case-insensitive collation at the server and table level, but case-sensitive on the column level.  Yes, I know that is not best practices and seems like a weird design choice but I have to support it as the DBA.

Normally this would require a script from the vendor because it is their code, but the product allows you to create some custom tables (they default to the table level collation) in the database and those are our responsibility. So why would you need to change it you say?  The developers were trying to write some reports against these tables and as you would expect they received an error (Msg 468, Level 16, State 9, Line 4 Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CS_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.)  It would take forever to go table by table and column by column to change the collation, hence the need for the script!

DISCLAIMER: I have had this little snippet for a while and I do not know who wrote it originally, probably found on MSDN.  I also know that it is not the most updated way of doing this (you should use the sys schema) but it works and sometimes that is easier than rewriting the code.

The first thing you should do is right-click on your query window in SSMS and set your results to text so that the script outputs you a new script that you will run in another query window.  Keep in mind with some of your tables you might have to drop and recreate the indexes in order to alter the collation.  Always take a backup before changing something like this and know what you are doing and changing before executing any script.

SELECT  ‘ALTER TABLE ‘ + syso.name + ‘ ALTER COLUMN [‘ + sysc.name + ‘]’,
syst.name + ‘ (‘ ,
sysc.length ,
‘) COLLATE SQL_Latin1_General_CP1_CS_AS’ ,
CASE sysc.ISNULLABLE
WHEN ‘1’ THEN ‘NULL’
ELSE ‘NOT NULL’
END + CHAR(13) + CHAR(10) + ‘GO’
FROM    sysobjects syso ,
syscolumns sysc ,
systypes syst
WHERE   syso.id = sysc.id
AND syst.xtype = sysc.xtype
AND sysc.collation = ‘SQL_Latin1_General_CP1_CI_AS’
AND syso.type = ‘U’
AND syst.name != ‘text’
AND syso.name NOT IN ( ‘SYSDIAGRAMS’ )

Enjoy and I hope this helps you!