Ah, collation. When you need it, you need it, but when you don’t, it likes to let you know that it’s still there sometimes. It’s easy to slip, trip and end up with collation mismatches if you don’t pay attention to the DDL you’re flinging around (I’m looking at you redgate).
If you find yourself in one of those “ah damn” moments when you’ve managed to get multiple collations in your database, the following script can help you sort out the mess. Run it, it’ll give you a nice rundown of what the default collation is for the database, and show you the ALTER TABLE scripts required to set the collation.
One small tweak you could make – change line 14 to be COLLATE database_default, rather than hard setting the default you’ve plucked from the database on line 1. It also isn’t foolproof, you’ll have to drop and recreate any indexes that use the fields that you’re about to switcho changeo the collation on.
That’s an exercise left to the reader.
declare @default nvarchar(128) = cast( DATABASEPROPERTYEX(DB_NAME(), 'Collation') as nvarchar(128))
select @default as Database_Default_Collation
SELECT
'ALTER TABLE ' + INFORMATION_SCHEMA.COLUMNS.TABLE_NAME +
'ALTER COLUMN ' + COLUMN_NAME + ' ' + DATA_TYPE +
CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 then '(max)'
WHEN DATA_TYPE in ('text','ntext') then ''
WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL
THEN '('+(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH)+')' )
ELSE isnull(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH),' ')
END
+' COLLATE ' + @default + ' ' +
CASE IS_NULLABLE
WHEN 'YES' THEN 'NULL'
WHEN 'No' THEN 'NOT NULL'
END
FROM
INFORMATION_SCHEMA.COLUMNS
INNER JOIN INFORMATION_SCHEMA.TABLES
ON INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = INFORMATION_SCHEMA.TABLES.TABLE_NAME
AND INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA = INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA
WHERE
DATA_TYPE IN ('varchar' ,'char','nvarchar','nchar','text','ntext')
AND TABLE_TYPE = 'BASE TABLE'
and COLLATION_NAME <> @default
