List Databases that have a different Collation than the SQL Server Instance

To list all databases and their collations, together with the SQL Server Instance collation, run this query:

SELECT
name as Database_Name
, DATABASEPROPERTYEX(NAME,'COLLATION') AS Database_collation
, SERVERPROPERTY('COLLATION') AS Server_Collation
FROM SYS.DATABASES

list_all_database_server_collation

To list only databases that have DIFFERENT collation that the SQL Server Instance collation, run this query:

SELECT
name as Database_Name
, DATABASEPROPERTYEX(NAME,'COLLATION') AS Database_collation
, SERVERPROPERTY('COLLATION') AS Server_Collation
FROM SYS.DATABASES
WHERE (DATABASEPROPERTYEX(NAME,'COLLATION')) <> SERVERPROPERTY('COLLATION')

list_different_database_server_collation

Read related article:
How to List all Databases in a SQL Server Instance