We have a server containing several databases with tables containing cross references, views, stored procedures, triggers etc.
The problem is that one database has the wrong collation.
We are now experiencing errors related to this, because "equal to"-queries are being run. We can stop the system for a while in order to change this collation, but we are not sure how to do it.
As I understand it, running:
ALTER database_name COLLATE collation_name
will not change the columns in the database, leaving us with the same problem.
My idea is to export all data to temporary tables, drop the old tables, create new ones with new collation, import data from temp tables.
The problem with this is that there are dependencies that run across the tables. Wouldn't I have to make sure all tables are created in the correct order? Would it be possible to make a query that runs through the database creating scripts that I could use to recreate the database?