Tag: collation

List Databases with a different collation than the SQL Server

ProtiguousLeave a comment 
select
    [d].[name] [Database Name]
   ,databasepropertyex( [d].[name], 'COLLATION' ) [Database Collation]
   ,serverproperty( 'COLLATION' ) [Server Collation]
from [sys].[databases] [d]
where ( databasepropertyex( [d].[name], 'COLLATION' )) != serverproperty( 'COLLATION' );

List collation on all columns in all tables in database on SQL Server

ProtiguousLeave a comment 
select
    concat( quotename( [s].[name] ), N'.', quotename( [t].[name] ), N'.', quotename( [c].[name] ) ) [Column Name]
   ,[c].[collation_name] [Collation]
from [sys].[schemas] [s]
join [sys].[tables] [t] on [t].[schema_id] = [s].[schema_id]
join [sys].[columns] [c] on [c].[object_id] = [t].[object_id]
where [c].[collation_name] is not null
order by [Column Name];

Example: