samot-dwarf (1/14/2016)
I doubt, that your real solution (specifying an explicit collation string at each char field) is really a real solution, because it is very error prone - you have to find EVERY code line with #temp tables (+ maybe @table-vars??? + every cross db access + access to some sys table/view joins as to sys.server_principals) and change it. Futuremore you have to rembember this every time you (or your new developer) writes a new procedure.Personally I would have used my script to create the drop / create statements for the FKs, dropped them, changed collation and recreated the FKs. Should be much lesser work and is a one time action that solves the problem for the future.
The solution is very real and works just fine. Been there, done that. 1000+stored procedures and functions across 3 active version branches. Adding the COLLATION DATABASE_DEFAULT to temp tables and table vars was easy, and maintaining/remembering it is a piece of cake. New developers seem to pick it up quickly, also
As for your solution, just dropping the FKs, changing the DB collation and recreating the FKs won't solve the problem.
The collation is stored with each and every string-based column (varchar, nvarchar, etc.) in every table. In order to truly change the collation of the database, you must also change the collation on each string column. In addition to dropping FKs, you must also drop any indexes, statistics, computed columns and check constraints that refer to the column in question. And, since you can only alter one column at a time, it would probably be easier to rename the table, create a new one, copy the data, and drop the old one.