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

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">