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