SQL Server: How to compare Server and Database Collation

Sometimes during an SQL Server audit, I check the collation of the server and the databases but every time separately.
The goal of this blog is to give you a script to have both and compare it.

First step is to have the server collation and the code page.
The code page will help to see if the character set is the same.
For example, for us, the 1252 is Latin/Western European and the 1258 is Vietnamese.
The collation is also good to have to see the case-sensitivity, accent- sensitivity, kanatype- sensitivity & width- sensitivity.

SELECT CONVERT (varchar, SERVERPROPERTY('collation')) AS 'server_collation',collationproperty(CONVERT (varchar, SERVERPROPERTY('collation')), 'codepage') AS 'server_codepage'

The second step is to have the collation from all databases with the view sys.databases:

SELECT db.name AS datebase_name, db.collation_name AS datebase_collation, collationproperty(db.collation_name, 'codepage') AS datebase_codepage FROM sys.databases AS db

The last step is to merge these 2 queries in one and have all information about collation and code page:

SELECT CONVERT (varchar, SERVERPROPERTY('collation')) AS 'server_collation',collationproperty(CONVERT (varchar, SERVERPROPERTY('collation')), 'codepage') AS server_codepage ,
db.name AS datebase_name, db.collation_name AS datebase_collation, collationproperty(db.collation_name, 'codepage') AS datebase_codepage FROM sys.databases AS db

To finish the script, you can add a clause “where” with the difference between the 2 code pages to have only what can be a problem:

SELECT CONVERT (varchar, SERVERPROPERTY('collation')) AS 'server_collation',collationproperty(CONVERT (varchar, SERVERPROPERTY('collation')), 'codepage') AS server_codepage ,
db.name AS datebase_name, db.collation_name AS datebase_collation, collationproperty(db.collation_name, 'codepage') AS datebase_codepage FROM sys.databases AS db
where collationproperty(CONVERT (varchar, SERVERPROPERTY('collation')), 'codepage') != collationproperty(db.collation_name, 'codepage')

I hope this script can help you to see the collation and code page for the server and all databases.