{"id":82103,"date":"2011-04-08T23:36:36","date_gmt":"2011-04-08T23:36:36","guid":{"rendered":"https:\/\/www.webstaging.red-gate.com\/simple-talk\/?p=73364"},"modified":"2018-12-12T12:11:45","modified_gmt":"2018-12-12T12:11:45","slug":"the-process-of-changing-the-collation-for-a-database-and-objects","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/the-process-of-changing-the-collation-for-a-database-and-objects\/","title":{"rendered":"The Process of Changing the Collation For a Database and Objects"},"content":{"rendered":"

Note: This is a \u201cclassic\u201d post lightly edited that I wrote a while back and a reader asked about it. It has nothing to do with the fact that LaRock downgraded my status on his site: <\/em>http:\/\/thomaslarock.com\/rankings\/<\/em><\/a><\/p>\n

The scenario that got me started on this path was that one of our servers was installed with the default "old faithful" SQL_Latin1_General_CP1_CI_AS collation, and the main servers were in a Windows collation Latin1_General_CI_AS based on the suggestion when installing Microsoft CRM many years ago.  Note too that if you have any columns that you specifically want in a different collation than the rest of the database, of any collations coded in your code you will need to manage that yourself.<\/p>\n

I do the process using the following simple, yet time consuming, process.  Simple might be a misstatement.  Perhaps straightforward:
 
1.  Make a copy\/script of the database, in a manner that does not specify collations.<\/strong>  I will be using Red-Gate SQL Compare for this task because it can remove collation information from object creation.  I use their snapshot capabilities to create a code only copy of the database.  If you have build scripts for your database that don’t include table drops\/creates, but have all constraints, that is as good too.  Currently I don’t know of a good tool to selectively script parts of a database (like all checks, defaults, etc without tables.  And I am a fan of the Red-Gate tool anyhow.  For full disclosure, the copy I am using was given to me by Red-Gate as an MVP.  I am not however being paid for an endorsement in any way. )
 
2.  Remove most code and constraints from the database.<\/strong>  I remove all of them using a set of procedures that you can download from http:\/\/drsql.org\/Documents\/utility.drop_objects_procs.zip<\/a> that you can use to remove most everything from the database.  (I drop CLR objects, but not assemblies and have not done any testing along these lines yet. I have not implemented a procedure for full text objects, so for that you will be on your own  We only have a single full text index in use at this point, so I haven’t looked into it fully yet.)
 
The procedures are in a utility schema, and they are:
 
utility.relationships$remove
utility.checkConstraints$remove
utility.defaultConstraints$remove
utility.indexes$remove
utility.uniqueConstraints$remove
utility.primaryKeyConstraints$remove
utility.codedObjects$remove –triggers, stored procedures, the lot can be removed
utility.columns$changeCollation
utility.computedColumns$remove –removes computed columns because they are based on columns that we have to change<\/p>\n

Each of these has a parameter combination (generally defaults or ‘%’ wildcards) that will let you get rid of everything in the database other than tables:
 
exec utility.checkConstraints$remove @table_schema = ‘%’
exec utility.defaultConstraints$remove @table_schema = ‘%’
exec utility.indexes$remove @table_name = ‘%’,@index_name = ‘%’
exec utility.uniqueConstraints$remove @table_schema = ‘%’, @table_name = ‘%’, @index_name = ‘%’
exec utility.codedObjects$remove @object_name = ‘%’
exec utility.computedColumns$remove @table_name = ‘%’, @column_name = ‘%’, @table_schema = ‘%’<\/p>\n

The script I ran to remove objects was basically very much a "dumb" process.  For example, I chose to just drop all indexes rather than only dropping indexes that involved columns with char datatypes.  In the future I might upgrade my index, unique and primary key constraint dropping code to discriminate between rows of different types.  Certainly if I have to do a larger database that is in production (something I luckily never had to do again.)
 
It will not drop computed columns, so you might have to do those drops manually.
 
Note, I didn’t do foreign keys or primary keys as I use identities for all keys in this database.  Hence they will not be affected by my script, but I do include a relationship drop procedure because I use it daily in a few drop and reload style ETL processes.
 
3.  Change the collations.<\/strong>  To do this, you simply alter each character column setting the new collation in the ALTER COLUMN statement.  This is built into my procedure, so I just run this:
 
exec utility.columns$changeCollation @fromCollation = ‘SQL_Latin1_General_CP1_CI_AS’,
                                     @toCollation = ‘Latin1_General_CI_AS’
                                    
If you have missed anything (like you didn’t remove coded objects, or you had full-text indexes that you needed to drop), the error handling of the utility procs basically returns the error as a result set, and in that message I include the statement that was tried to be executed.  If I didn’t screw something up (like the first time I ran it I had messed up the quotename() usage to return [varchar(20)] instead of [varchar](20).  That looks funny, but obviously the other way was thinking it was a user-defined type of "varchar(20)"
 
The procedure basically is a cursor on the following statement
 
Declare @fromCollation sysname,
            @toCollation sysname
select   @fromCollation = ‘SQL_Latin1_General_CP1_CI_AS’ –or whatever
select   @toCollation = ‘Latin1_General_CI_AI’  –or whatever <\/p>\n

SELECT ‘ALTER TABLE ‘ + quotename(TABLE_NAME) +
            ‘ ALTER COLUMN ‘ + quotename(COLUMN_NAME) + ‘ ‘ + quotename(DATA_TYPE) +
            CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 then ‘(max)’
                     WHEN DATA_TYPE in (‘text’,’ntext’) then ”
                     WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL
                                THEN ‘(‘+(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH)+’)’ )
                  ELSE isnull(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH),’ ‘) END
             +’ COLLATE ‘ + @toCollation+ ‘ ‘ + CASE IS_NULLABLE
                                                                           WHEN ‘YES’ THEN ‘NULL’
                                                                           WHEN ‘No’ THEN ‘NOT NULL’
                                                                END
FROM   INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN (‘varchar’ ,’char’,’nvarchar’,’nchar’,’text’,’ntext’)
    and  COLLATION_NAME like @fromCollation <\/p>\n

 
4.  Change the database collation <\/strong>(or this will be all for naught when people try to create objects and you will have to try again.)
 
alter database <databaseName>
     set single_user with rollback immediate
go
alter database <databaseName>
     collate Latin1_General_CI_AS
go
alter database <databaseName>
     set multi_user <\/p>\n

 
5. Now I just run the scripts to recreate all of the objects, or compare the RedGate snapshot, etc.<\/strong>  Make sure that you don’t include the old collation in any of your scripts\/comparisons, otherwise you will lose all of your hard work \ud83d\ude42
 
6. Finally I drop the utility objects. <\/strong>Then the database is back to where it stared, with different collations for all of the text data.<\/p>\n

Good luck! <\/p>\n","protected":false},"excerpt":{"rendered":"

Note: This is a \u201cclassic\u201d post lightly edited that I wrote a while back and a reader asked about it. It has nothing to do with the fact that LaRock downgraded my status on his site: http:\/\/thomaslarock.com\/rankings\/ The scenario that got me started on this path was that one of our servers was installed with……<\/p>\n","protected":false},"author":56085,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[19684],"class_list":["post-82103","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82103","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/56085"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=82103"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82103\/revisions"}],"predecessor-version":[{"id":82277,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82103\/revisions\/82277"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=82103"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=82103"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=82103"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=82103"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}