CODEWITHSUNDEEP
sql-serversql-server-2005collation
Rich
Rich

Reputation: 3821

In SQL Server 2005 is there any way to change the collation of a whole database at once?

Using SQL Server Management Studio, I have backed up a database from one server and restored it to my local copy of SQL Server 2005. But the collation of the backed up database is SQL_Latin1_General_CP1_CI_AI and I am attempting to compare columns to a database which is Latin1_General_CI_AI.

The database has a lot of keys and constraints which makes changing each column individually throw errors.

What is the best way to change the collation of a whole database in SQL Server 2005?

Upvotes: 2

Views: 559

Answers (2)

Akram Shahda
Akram Shahda

Reputation: 14771

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.

Refer to:

Setting and Changing the Database Collation

Setting and Changing the Column Collation

Upvotes: 0

pabdulin
pabdulin

Reputation: 35219

There is no simple way, you must change collation of Db (ALTER DATABASE), and all columns individually (ALTER TABLE). Maybe there is a ready to use tools, maybe you need to write it youself. Maybe Data Comparison Tool of Visual Studio 2010 can be useful (Data -> Data Compare menu).

Upvotes: 3

Related Questions