Sql-server – SQL Server: TSQL or safe steps needed: Change collation en masse for group of tables

collationsql server

Looking for a safe TSQL (preferred) or safe steps (rather automated if possible) to change collation on dozens of tables in one throw.

Background:

We have a db, SQL Server 2005, with ~100 tables.

30-40 of them have a collation set which is inappropriate for the app we are running. (we need them all to be "SQL_Latin1_General_CP1_CS_AS" but this group of tables is "SQL_Latin1_General_CP1_CI_AS")

Possible Solution:

We found this script:
http://www.codeproject.com/Articles/12753/SQL-Server-2000-Collation-Changer

Issue with found solution:

But this script appears to introduce the possibility of data loss, AND it appears to neither detect nor warn nor measure if there will be data loss.

Feels risky to use!

Question to Community:

Is there a more advanced way of tackling this?

Preferably, a script that:

  • Detects if there will be data loss and at least gives a warning. (Even a script which just scans tables, considering change from collation A to B, and tells me what will happen if this change is made…)
  • Can work in bulk (e.g. I tell it what my target collation is, and it does all the work against all tables and columns in the db and reports back what it did)

Thank you!

Best Answer

I had to do this recently and it was a big pain. I ended up doing a write up on changing collations: Collation: Correction -> Expression, Column, Database

My personal preference for method in your situation would probably be to

  1. Create a new database (with the correct collation if needed)
  2. Script out the old database
  3. Do a search and replace on the script file to change the collation from one to another
  4. Run the script on the new database
  5. Move the data across
  6. Do a rename swap to put the new database in place of the old

Your other options are to either create a script that generates alters for each column or to use the GUI. The GUI is going to be slower but more certain. The script method generates the risk of making changes you didn't intend if your script isn't letter perfect.

The script method also has the problem that you have to drop any constraints, indexes etc that touch the column before you can alter it to change the collation. You might be able to generate a script to do that for you but it wouldn't be easy.

If you decide to go the script method I can probably help you generate one to create the alters but for dropping and re-creating indexes/constraints you would be on your own. Like I said I would go with the re-create the database method. It's easy and relatively fast unless you have a huge amount of data. The only real drawback is that it requires there to be double or even triple (space for logs) the size of your database free.

Related Question