Looking for a safe TSQL (preferred) or safe steps (rather automated if possible) to change collation on dozens of tables in one throw.
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")
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)