Cool Tips‎ > ‎Data Type‎ > ‎

Data Type - Comprehensive Collation Checker

Applicability:

                 SQL Server 2000:        Tested
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    Unknown
Date:        22 Nov 2007

Description

There is nothing more scary (or depressing) for an experienced DBA to discover than that there are different collations in different locations on the server.  The scope for collation differences has expanded across the SQL Server versions.  Explaining this here would be quite involved.
The bottom line is that changing any collation setting at the database or server level is extremely challenging and can have unexpected results.
Setting of the server level collation is usually best remedied with a reinstall, but can be done by rebuilding the master database.  This is the least common problem and usually the person to blame is the DBA
Setting of the DB or object level collation can be done easily by a sysadmin, developer with the right privileges or by an errant piece of 3rd party software.
 
Problems with collation, in my experience, either surface as a direct collation mismatch error very quickly, or will be hidden until a process performs text manipulation activities in TempDB.  For a highly numeric application, the latter activity can be an infrequent occurrence.
 
In my opinion, while the flexibility of collations at multiple levels can be a boon, especially in these days of high volume or high complexity data interchange between systems, however, it is very much a double-edged sword as when problems arise they can be a real pain to fix.
 
The answer is to keep a close eye on all your DB servers to ensure that collation mismatches are caught early.
 
The following code performs a range of collation checks:
  • Mismatch collations between user databases and master database
  • Multiple collations within a database
  • Mismatch collations between columns and database
 

Code

USE MASTER
GO

DROP PROCEDURE [dbo].[usp_Collation_Checker]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_Collation_Checker]
AS
-- this procedure checks the collation as follows :-
-- databases that have different collations from server
-- databases that have different collations of columns within
-- databases that have column collations different from database collation
--
-- it returns some text with a description of the issue e.g :-
-- "there are no databases that have a different collation from master"
-- "the pgr_test database has more than one collation"
-- "the col2 column collation (Latin1_General_CS_AS) in the pgr_test database does not match
-- the database collation (Latin1_General_CI_AS)"
--
-- this procedure has been tested on sql server 2000 sp4 and SQL Server 2005 sp2
-- any problems email pgr_consulting @ yahoo.com

SET NOCOUNT ON

CREATE TABLE
#Databases
  
(
      
dbid        INT IDENTITY(1,1),
      
dbname      VARCHAR(100),
      
collation   VARCHAR(100)
   )

CREATE TABLE #Database_Collations
  
(  
      
dbname VARCHAR(100),
      
collation VARCHAR(100)
   )
  
CREATE TABLE #Database_Collations_by_Column
  
(
      
dbname VARCHAR(100),
      
colname VARCHAR(100),
      
collation VARCHAR(100)
   )


DECLARE
  
@Msg                VARCHAR(2000),
  
@Master_Collation   VARCHAR(255),
  
@Number_of_DBs      INT,
  
@Counter            INT,
  
@SQL                VARCHAR(8000),
  
@DBName             VARCHAR(100)


-- Section #1 - Get all DBs with a different collation to the master database
SELECT @Master_Collation=CONVERT(SYSNAME,DATABASEPROPERTYEX('master','Collation'))

SELECT
  
'the '
  
+ name
  
+ ' database has a different collation from master, '
  
+ CONVERT(SYSNAME,DATABASEPROPERTYEX(name,'Collation'))
   +
' (master='
  
+ @Master_Collation+')'          AS 'Server/Database Collation Mismatch Check'
INTO #temp_collations
FROM sysdatabases
WHERE CONVERT(SYSNAME,DATABASEPROPERTYEX('master','Collation')) <>
      
CONVERT(SYSNAME,DATABASEPROPERTYEX(name,'Collation'))
ORDER BY name

IF @@ROWCOUNT = 0
BEGIN
   SELECT
'There are no databases that have a different collation from master'
  
AS 'Server/Database Collation Mismatch Check'
END
ELSE
BEGIN
    SELECT
[Server/Database Collation Mismatch Check]
    
FROM #temp_collations
END


-- Section #2 and #3 - Get all collation information within each DB (table and column)
INSERT INTO #Databases
SELECT
  
name,
  
CONVERT(SYSNAME,DATABASEPROPERTYEX(name,'Collation')) AS collation
FROM MASTER..sysdatabases
ORDER BY name

SELECT @Number_of_DBs = COUNT(*) FROM #Databases

SELECT @Counter=1

WHILE @Counter <= @Number_of_DBs
BEGIN

   SELECT
@DBName = dbname
  
FROM #Databases
  
WHERE dbid=@Counter

  
SELECT @SQL=
          
'insert into #Database_Collations select '''
          
+ @DBName
          
+ ''' as dbname, sc.collation from '
          
+ @DBName
          
+ '..syscolumns sc, '
          
+ @DBName
          
+ '..sysobjects so, '
          
+ @DBName
          
+ '..systypes st
where so.id=sc.id
and so.type=''U''
and st.xtype=sc.xtype
and sc.xtype in (select xtype from systypes
where name in (''char'',''nchar'',''nvarchar'',''varchar''))
and so.name not like ''dt%''
group by sc.collation'
    
  
EXEC (@SQL)

  
SELECT @SQL=
          
'insert into #Database_Collations_by_Column select '''
          
+ @DBName
          
+ ''' as dbname, sc.name , sc.collation '
          
+ ' from '
          
+ @DBName
          
+ '..syscolumns sc, '
          
+ @DBName
          
+ '..sysobjects so, '
          
+ @DBName
          
+ '..systypes st
where so.id=sc.id
and so.type=''U''
and st.xtype=sc.xtype
and sc.xtype in (select xtype from systypes
where name in (''char'',''nchar'',''nvarchar'',''varchar''))
and so.name not like ''dt%''
group by sc.name, sc.collation'
    
  
EXEC (@SQL)

  
SELECT @Counter=@Counter+1

END

--Section #2 - identify databases with more than one collation
SELECT
      
'The '
      
+ dbname
      
+ ' database has more than one collation' AS 'Multiple Colllations in One Database Check'
INTO #Different_Collations
FROM #Database_Collations
GROUP BY dbname
HAVING COUNT(*)>1

IF @@ROWCOUNT=0
BEGIN
    SELECT
'There are no databases containing multiple collations.'
    
AS 'Multiple Collations in One Database Check'
END

ELSE

BEGIN
    SELECT
[Multiple Collations in One Database Check] FROM #Different_Collations
END


-- Section #3 - identify databases with column collations different from database collation
SELECT
      
'The '
      
+ dc.colname
      
+ ' column collation ('
      
+ dc.collation
      
+ ') in the '
      
+ dc.dbname
      
+ ' database does not match the database collation ('
      
+ d.collation
      
+ ')'
AS 'Database/Column Collation Mismatch Check'
INTO #Column_Collations
FROM #Database_Collations_by_Column dc
INNER JOIN #Databases d
  
ON dc.dbname = d.dbname
WHERE dc.collation <> d.collation

IF @@ROWCOUNT=0
BEGIN
    SELECT
'There are no databases that have columns that do not match its database collation'
    
AS 'Database/Column Collation Mismatch Check'
END
ELSE
BEGIN
    SELECT
[Database/Column Collation Mismatch Check]
    
FROM #Column_Collations
END

-- tidy up
DROP TABLE #Databases
DROP TABLE #Column_Collations
DROP TABLE #Database_Collations
DROP TABLE #Different_Collations
DROP TABLE #Database_Collations_by_Column
GO


ċ
Collation Checker.sql
(5k)
Andy Hughes,
Jun 23, 2012, 1:38 AM
Comments