The following script will help you to get all the columns with different collation than the one specified as default for a database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
DECLARE @DatabaseCollation NVARCHAR(100); SET @DatabaseCollation = (SELECT collation_name FROM sys.databases -- Use DB_ID() when you are running the query against the intended database or DB_ID('database') for others WHERE database_id = DB_ID()); -- View the collation of the database SELECT @DatabaseCollation AS 'Default database collation'; SELECT t.Name AS [Table] ,c.name AS [Column] ,ty.name AS [ColumnType] ,c.max_length AS [MaxLength] ,c.collation_name AS [Collation] ,c.is_nullable AS [Nullable] FROM sys.columns AS c INNER JOIN sys.tables AS t ON c.object_id = t.object_id INNER JOIN sys.types AS ty ON c.system_type_id = ty.system_type_id -- Exclude system tables WHERE t.is_ms_shipped = 0 -- Include only columns with a different database collation AND c.collation_name <> @DatabaseCollation; |