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
|