SQL Script To Locate Columns With Different Collation

May 1st, 2014 by

A while ago I posted about a problem with a collation conflict on a couple of columns in the Tax table. It seems I posted about how to fix the problem, but it seems I didn’t post how I found the problem columns.

I did this with a fairly simple SQL script:

DECLARE @Collation SYSNAME SET @Collation = 'SQL_Latin1_General_CP1_CI_AS'

   TABLE_NAME AS 'Table'
   ,COLUMN_NAME AS 'Column'
   ,DATA_TYPE AS 'Data Type'
   ,COLLATION_NAME AS 'Collation Name'
   DATA_TYPE IN ('varchar','char','nvarchar','nchar','text','ntext')
   COLLATION_NAME <> @Collation

Source: azurecurve

Leave a Reply