We have a situation where the collation of databases, tables, and some columns are different between our dev and production SQL Servers, and it's wreaking havoc on development. Things will work on dev and then break due to collation conflicts when promoted, data and structures will be copied from prod to dev which in turn breaks the queries on dev because of conflicts, etc. We'll work around it by either explicitly defining the COLLATION option in the query sometimes or setting the collation of each column in an offending table to work around it. The former seems to have a performance hit, and the latter is a PITA.
Is there any script or anything that can show collation discrepancies or information on a database and objects, or any suggestions on ways to automatically reconcile differences so we could get things in sync?
Additionally, any tips on debugging collation conflicts within a query would be nice, too....I never know where the conflict actually is and end up having to line by line go through and override it....sigh....it's a mess.
Thanks for any ideas!