Collation Mismatch : I think it's one of those deja vu things

This error surfaced once again today...

Msg 468, Level 16, State 9, Server SERVER1, Line 6 Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation. 

Here is a quick query of looking at COLLATION and COMPATIBILITY LEVEL differences between a server and the databases hosted on it. I'm looking at compatibility level too as in my case I correctly suspected that the databases concerned were migrated from another server.

SELECT 
  @@SERVERNAME AS ServerName
 ,SERVERPROPERTY('Collation') AS ServerCollation
 ,(10 * (CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), CHARINDEX('.', CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), 1) - 1) AS INTEGER))) AS ServerVersion
 ,[Name] AS DBName
 ,DATABASEPROPERTYEX([Name],'Collation') AS DBCollation
 ,[cmptlevel] AS DBCompatibilityLevel
 ,CASE WHEN SERVERPROPERTY('Collation') <> DATABASEPROPERTYEX([Name],'Collation') THEN 'Mismatch' ELSE 'Match' END AS CollationSettings
 ,CASE WHEN (10 * (CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), CHARINDEX('.', CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), 1) - 1) AS INTEGER))) <> [cmptlevel] THEN 'Mismatch' ELSE 'Match' END AS CompatibilitySettings
FROM sysdatabases

I can actually solve my specific problem by using a COLLATE clause on the join condition (as demonstrated back in 2007).

This is because I'm querying SQL System tables across databases (the master and user databases having different collations having the user databases being migrated from another server).

I think it's one of those deja vu things, an article I wrote on Collation for SQL Server Club (just don't talk about SQL Server Club).