SQL Server - is there a way to mass resolve collation conflicts

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 arou开发者_运维技巧nd 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!

Generally, use "COLLATE DATABASE_DEFAULT" on temp table, table variables etc

Collation conflicts usually happen you have server vs database collation conflicts, server in this case being tempdb.

Frankly, I would make an effort to align your collations and have them all one per database.

If you have mix and match across servers, databases and columns then there is no magic bullet. You could start by looking for all COLLATE clauses in code (see above) followed by ensuring all columns have collation removed....

Your first step is probably deciding on your standard for what collation you should be using. If you are going to be supporting multiple collations due to international requirements for your application then decide which ones you will support and how you plan to test for all collations.

The first step here is deciding operationally what you'll be doing, not the technical solution. Once you've done that, you're going to have to bite the bullet and go through all of your tables, stored procedures, etc. and get all of them in sync on ALL of your servers and in any source code that you have in your source control system. Ideally your queries will work without referring to any collation or doing any conversions to wrestle things into working.

To help you find all of your collation issues, you can start looking at this in all of your databases:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

I have had great success using this tool http://www.codeproject.com/KB/database/ChangeCollation.aspx