I don't completely understand collations, to be honest. And unfortunately we don't have a DBA where I work to help guides us through things like this. The problems began when the developers all received new workstations. The local SQL Server install used a different collation than our staging server. I don't specifically remember what the issues were, just error messages when running stored procedures. Some tables had a specific collation applied to them that was different than the database collation. Its all quite messed up to be honest. But we found that as long as we use scripts to generate the database we didn't encounter the issues.

I wrote the following script to modify the collation for each table in a database, but didn't have the confidence in its implications to ever run it anywhere but on my local DB.

DECLARE @DB_Collation nvarchar(50)

SET @DB_Collation = CAST(DATABASEPROPERTYEX('TargetDbName', 'Collation') as nvarchar)

-- Display the rows before the updated

SELECT *

FROM INFORMATION_SCHEMA.COLUMNS c

WHERE (

COLLATION_NAME IS NOT NULL AND

COLLATION_NAME != @DB_Collation AND

EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_NAME = c.TABLE_NAME AND TABLE_TYPE = 'BASE TABLE')

)

DECLARE dynamicSqlCursor CURSOR

FOR SELECT 'ALTER TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' + char(10) +

'ALTER COLUMN ' + COLUMN_NAME + ' ' + DATA_TYPE +

CASE WHEN DATA_TYPE = 'nvarchar' OR DATA_TYPE = 'varchar'

THEN '(' +

CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1

THEN 'MAX'

ELSE CAST(CHARACTER_MAXIMUM_LENGTH as varchar)

END + ')'

END + char(10) + 'COLLATE ' + @DB_Collation + char(10) +

CASE IS_NULLABLE

WHEN 'NO' THEN 'NOT NULL'

WHEN 'YES' THEN 'NULL'

END + char(10)

FROM INFORMATION_SCHEMA.COLUMNS c

WHERE (

TABLE_SCHEMA != 'dbo' AND -- Avoid system tables

COLLATION_NAME IS NOT NULL AND -- Null collation will fall back on database collation anyways

COLLATION_NAME != @DB_Collation AND -- This is our true target: old collation values

EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_NAME = c.TABLE_NAME AND TABLE_TYPE = 'BASE TABLE') -- Alter tables only

)

DECLARE @SQL nvarchar(MAX)

OPEN dynamicSqlCursor

FETCH NEXT FROM dynamicSqlCursor

INTO @SQL

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT @SQL

-- Execute the query

EXEC sp_executeSql @SQL

FETCH NEXT FROM dynamicSqlCursor

INTO @SQL

END

CLOSE dynamicSqlCursor

DEALLOCATE dynamicSqlCursor

-- Verify the results. The following query should return 0 rows.

SELECT *

FROM INFORMATION_SCHEMA.COLUMNS c

WHERE (

COLLATION_NAME IS NOT NULL AND

COLLATION_NAME != 'SQL_Latin1_General_CP1_CI_AS' AND

EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_NAME = c.TABLE_NAME AND TABLE_TYPE = 'BASE TABLE')

)