SDU Tools: List Mismatched Database Collations in SQL Server
Anyone who’s worked with SQL Server for any length of time has probably run into collation issues. They will have seen something like this:
SQL Server Error: Cannot resolve the collation conflict
In many cases, it will be a difference between the collation in tempdb and their own database, they’ve created temporary tables, and tried to join them to tables in their database. And it goes bang !
While you can fix it in the query by specifying a COLLATE clause, it’s a pain in the neck. If you use temp tables, you really should have this when working with them so your application isn’t dependent upon the server’s collation.
But one of the things you might need to check is which databases have collations that are different to the server’s collation. In our free SDU Tools for developers and DBAs, we added a procedure ListMismatchedDatabaseCollations to do just that.
You can see how to execute it in the main image above. The procedure takes these parameters:
@ExcludeSystemDatabases bit - Do you want to omit any system databses from this list? @ExcludeReportingServicesDatabases bit - Do you want to omit any SQL Server Reporting Services databases from this list?
One row is returned for each database.
The columns returned are DatabaseName, DatabaseCollation, and ServerCollation.
You can see it in action here:
To become an SDU Insider and to get our free tools and eBooks, please just visit here:
http://sdutools.sqldownunder.com
2018-11-14