How do I change the collation of a SQL Server Database?

I'm trying to standardise all databases on a single collation - Latin1_General_CI_AS (the standard collation). I have some databases that are in SQL_Latin1_General_CP1_CI_AS.

I know I can use ALTER DATABASE to change the database collation, but that only affects new objects. My understanding is that the only way to change existing columns is to do ALTER COLUMN on each column in every table - and I would need to drop and recreate all the indexes to do even that.

I guess it would look something like this:

DROP INDEX indexname ON tablename

GO

ALTER TABLE tablename ALTER COLUMN columname varchar(50) COLLATE Latin1_General_CI_AS NULL

GO

CREATE CLUSTERED INDEX indexname ON tablename (columname ASC)

and repeat for every varchar, char, text, nvarchar, nchar and ntext column in the entire database. That would be an enormous SQL script.

Is there an easier way to do this, or can anyone suggest a way to automate the creation of a SQL script to do it?

Solution 1:

MS KB 325335 has options on how to do this for the whole db and all columns.

Basically:

Script database tables (with new collation)
DTS/SSIS data (watching collation)
Add constraints

Solution 2:

It might need some tweaking, but I had success using the "SQL Server 2000 Collation Changer" utility: http://www.codeproject.com/KB/database/ChangeCollation.aspx

Solution 3:

Unfortunately this is not an easy task in SQL Server.

You could use a scripting tool like Redgate's SQL Compare for your existing database objects (tables, stored procedures, views etc.) If you don't have a license, you could using a the free trial. After you've created the new database with the right collation and rebuilt the objects from your script, you could run SSIS to transfer data from one database to another. If you have lots of data, use T-SQL bulk insert.

For having the right collation for future databases on that server, you could change the default collation on the server. The following MSDN article explains what changes using the COLLATE clauses of ALTER DATABASE and ALTER TABLE:

Setting and Changing the Database Collation (SQL Server 2008 Books Online)

You can change the collation of any new objects that are created in a user database by using the COLLATE clause of the ALTER DATABASE statement. This statement does not change the collation of the columns in any existing user-defined tables. These can be changed by using the COLLATE clause of ALTER TABLE.

When you change the database collation, you change the following:

The default collation for the database. This new default collation is applied to all columns, user-defined data types, variables, and parameters subsequently created in the database. It is also used when resolving the object identifiers specified in SQL statements against the objects defined in the database.

Any char, varchar, text, nchar, nvarchar, or ntext columns in system tables are changed to the new collation.

All existing char, varchar, text, nchar, nvarchar, or ntext parameters and scalar return values for stored procedures and user-defined functions are changed to the new collation.

The char, varchar, text, nchar, nvarchar, or ntext system data types, and all user-defined data types based on these system data types, are changed to the new default collation.

Solution 4:

Generally it is not recommended to do this to a live server. The last time I looked, doing this was not officially supported by Microsoft. To do this in practice, you have to create a new instance with the right collation and migrate the database to it.

Restore of a DB with to a server with a different default collation causes all sorts of fun as tempdb will have the collation of the new server, so this is also not recommended.

Solution 5:

Try this utility, that given a source database will generate all scripts needed to be applied on a target database so collation is safely changed.