6

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!

Improve this question
6
  • I had to do this once; it was a pain. After I had changed each column's collation it was only after the values had been written that the new collation came into effect. I ended up updating character columns to themselves. If you have multi-million rows it may be better to create parallel tables with the proper collation, transfering data and swapping names once complete. Good luck.
    – Michael Green
    May 13, 2014 at 12:29
  • Me too, but it was back at SQL Server 7 time. I created a query to find and update each discrepant collation at char like columns in a database. Needless to say I updated system tables supposed to be let untochable. It was a pain and a mess and off course I tested a lot over a spare server. Depending on your particular scenario it can be a lot better to just use collation hints at your queries
    – jean
    May 13, 2014 at 17:18
  • I don't see why this has been put on hold - it's a perfectly valid question i.e. is there a simple way to change the collation on individual columns/tables en masse. The answer is 'no, but people might be able to point to a sensible script that has been written and can be adapted. I have such a script and think that it would provide at least a partial answer (the OP could adapt it).
    – Steve Pettifer
    May 14, 2014 at 12:00
  • Note to OP: Change your question to say "Is there an intrinsic way to do this with SSMS or should I look to a scripted solution?" or somesuch to satisfy the moderators. I think this is a quite common scenario and one which could do with a decent answer.
    – Steve Pettifer
    May 14, 2014 at 12:05
  • @StevePettifer Thanks. OP enhanced. (seems really strange that an up the middle dba question like this would be put on hold.) Note you can vote to reopen, and can also hit "flag" asking the sysops to reconsider.
    – Jonesome Reinstate Monica
    May 14, 2014 at 18:34

2 Answers 2

Reset to default
3

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.

Improve this answer
1
  • Ken, the key issue is: There appears NOT to be a tool that actually does the job. Not built in to SSMS, not a proven tool. Nothing, it appears. We have moved the db to SQL 2016, and we still need to adjust the collation, and are rather up a creek.
    – Jonesome Reinstate Monica
    Jul 4, 2017 at 5:14
0

The answer is in: Use Alex Baker's collation changing tool. It creates the script for you, and you then run the script. It drops all indexes, PKs and FKs, changes the collations on all objects, recreates all indexs, PKs and FKs and is basically just the magic needed.

This tool is now on github: https://github.com/alexmbaker/sqlcollation/tree/john-hardin-feedback

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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