Detecting & Fixing Collation Issues

Differences between database and server collations is a common DBA problem especially if the environment has developed organically over years. This is exactly the problem my team faces as we begin our data warehouse development. We're lucky in the sense that we're upgrading and consolidating old SQL Server instances to 2008 R2. This provides an excellent opportunity to standardise the server and database collations although it does require extra work as some legacy apps will need to be thoroughly tested.

We found a two useful scripts that help us identify and synchronise our databases which had collation differences.

The first is from respected SQL Blogger Tibor Karaski and identifies tables and columns within a database that do not match the database collation. Download this script here.

1:  IF OBJECT_ID('tempdb..#res') IS NOT NULL   
2:    DROP TABLE #res  
3:  GO  
4:  DECLARE @db SYSNAME,  
5:    @sql NVARCHAR(2000)  
6:  CREATE TABLE #res  
7:    (  
8:     server_name SYSNAME,  
9:     db_name SYSNAME,  
10:     db_collation SYSNAME,  
11:     table_name SYSNAME,  
12:     column_name SYSNAME,  
13:     column_collation SYSNAME  
14:    )  
15:  DECLARE c CURSOR  
16:  FOR  
17:  SELECT name  
18:  FROM  sys.databases  
19:  WHERE  NAME NOT IN ('master','model','tempdb','msdb') AND  
20:      state_desc = 'ONLINE'  
21:  OPEN c  
22:  WHILE 1 = 1   
23:    BEGIN  
24:      FETCH NEXT FROM c INTO @db  
25:      IF @@FETCH_STATUS <> 0   
26:        BREAK  
27:      SET @sql = 'SELECT  
28:    @@SERVERNAME AS server_name  
29:   ,''' + @db + ''' AS db_name  
30:   ,CAST(DATABASEPROPERTYEX(''' + @db + ''', ''Collation'') AS sysname) AS db_collation  
31:   ,OBJECT_NAME(c.object_id, ' + CAST(DB_ID(@db) AS SYSNAME) + ') AS table_name  
32:   ,c.name AS column_name  
33:   ,c.collation_name AS column_collation   
34:  FROM ' + QUOTENAME(@db) + '.sys.columns AS c  
35:   INNER JOIN ' + QUOTENAME(@db) + '.sys.tables AS t ON t.object_id = c.object_id  
36:  WHERE t.type = ''U''  
37:   AND c.collation_name IS NOT NULL  
38:   AND c.collation_name <> CAST(DATABASEPROPERTYEX(''' + @db + ''', ''Collation'') AS sysname)  
39:  '  
40:  --PRINT @sql  
41:      INSERT INTO #res  
42:          EXEC (@sql)  
43:    END  
44:  CLOSE c  
45:  DEALLOCATE c  
46:  SELECT *  
47:  FROM  #res  

The second script is much bigger and I can't remember where we found it online. If you wrote it I'm happy to reference you here as I certainly can't claim it.

The script will amend a database and the objects within it to a collation of your choice. Line 68 is where you set the variable for the collation you're changing to and line 70 is the database you're altering. It takes time to run as there are some complex conversions involved.

DO NOT RUN THIS SCRIPT IN A PRODUCTION ENVIRONMENT BEFORE TESTING EXTENSIVELY. By publishing this script I am not guaranteeing it is safe to use in your environment and I'm not advocating using it without first understanding it. However, it worked really well for us.

Here is a link to the script.