Note: This is a “classic” 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 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.

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.  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.  I remove all of them using a set of procedures that you can download from http://drsql.org/Documents/utility.drop_objects_procs.zip 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

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 = ‘%’

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.  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

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

 
4.  Change the database collation (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

 
5. Now I just run the scripts to recreate all of the objects, or compare the RedGate snapshot, etc.  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 🙂
 
6. Finally I drop the utility objects. Then the database is back to where it stared, with different collations for all of the text data.

Good luck!