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