Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
add comment

6 Answers 6

up vote 7 down vote accepted

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
share|improve this answer
    
The nice scripty bit was what I'd missed - there are good tools for creating scripts from existing data. –  Richard Gadsden Jun 4 '09 at 13:19
add comment

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

share|improve this answer
add comment

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.
share|improve this answer
add comment

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.

share|improve this answer
add comment

You could use the Infomation_Schema views to generate a script easily enough, but I'm not sure this is the best way to solve your problem. Re-creating all indexes on a largish db could take an enormous amount of time/log space etc. I'd go with migrate (not restore) to a new instance.

Dave J

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.