Find non-default collation on columns for all tables in SQL Server

Question

Is there a script I can use to find all columns in all tables in a SQL catalog that does not have the collation <database default> ?

I have taken over a legacy system and have different collations in the some tables and I would like to find all instances in one hit rather than going through manually.

However I don't want to change the collation programmatically as I would like to review each table one by one.

Answer 1

Try this script here:

DECLARE @DatabaseCollation VARCHAR(100)  SELECT      @DatabaseCollation = collation_name  FROM      sys.databases WHERE      database_id = DB_ID()  SELECT      @DatabaseCollation 'Default database collation'  SELECT      t.Name 'Table Name',     c.name 'Col Name',     ty.name 'Type Name',     c.max_length,     c.collation_name,     c.is_nullable FROM      sys.columns c  INNER JOIN      sys.tables t ON c.object_id = t.object_id INNER JOIN      sys.types ty ON c.system_type_id = ty.system_type_id     WHERE      t.is_ms_shipped = 0     AND      c.collation_name <> @DatabaseCollation 

It checks for the database default collation and then finds any columns that don't comply with that.