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

Solution 1:

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

Basically:

1. Script database tables (with new collation)
2. DTS/SSIS data (watching collation)
3. 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.