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