Purpose: This script will identify tables on all databases on a server that have differing collations, collation differences have probably occured between upgrades to SQL Server versions or where the server default differs

Note: The impact of collation differences may be found amongst other misellaneous events when importing, joining tables in queries, where clauses, funcations, data sorting and printing. Likely error message "Cannot resolve collation conflict for equal to operation".

Without a Fix you will need to specify the collation to convert to the same in all queries when collation not equal or an error message will result ie:-

SELECT ID

FROM ItemsTable

INNERJOIN AccountsTable

WHERE ItemsTable.Collation1Col COLLATE DATABASE_DEFAULT

= AccountsTable.Collation2Col COLLATE DATABASE_DEFAULT

To find the default collation of a database use:-

USEMASTER

GO

SETNOCOUNTON

/*Find database default*/

DECLARE @name sysname,

@Collate varchar(50)

SET @name =db_name(7)

-- These props only available if db not shutdown

SET @Collate =(SELECTconvert(sysname,DatabasePropertyEx(@name,'Collation')))

PRINT'Server Collation is '+@Collate

/*--------------------------------------------------------------------------------------------------

Purpose: This script will identify tables on all databases on a server that have differing collations

Author: Carolyn Richardson

Date: 05/10/2007

--------------------------------------------------------------------------------------------------*/

USE MASTER

GO

SET NOCOUNT ON

DECLARE @DB VARCHAR(150),

@Counter INT,

@Rec VARCHAR(150),

@SQL VARCHAR(1000),

@SQL1 VARCHAR(1000),

@SQL2 VARCHAR(1000)

SELECT database_id, name INTO #Temp

FROM sys.databases

WHERE name NOT IN ('Master','tempdb','msdb','model')

SET @Counter =(SELECT MIN(database_id) FROM #Temp)

/*Work out if a database has more than one collation, assumes only interested if */

CREATE TABLE #ctr

( NumRows int)

WHILE @Counter <=(SELECT MAX(database_id) FROM #Temp)

BEGIN

SET @DB =(SELECT name FROM #Temp

WHERE database_id = @Counter)

SET @SQL ='INSERT INTO #ctr SELECT count(distinct COLLATION_NAME)

FROM '+ @DB +'.INFORMATION_SCHEMA.columns

WHERE COLLATION_NAME LIKE ''%Latin1%'' '

EXEC (@SQL)

SET @Rec =(SELECT NumRows FROM #ctr)

DELETE FROM #ctr

IF(@Rec > 1)

BEGIN

PRINT @DB

SET @SQL1 ='SELECT TABLE_CATALOG AS [DATABASE], ' SET @SQL1 = @SQL1 +'TABLE_NAME, ' SET @SQL1 = @SQL1 +'COLLATION_NAME, ' SET @SQL1 = @SQL1 +'COLUMN_NAME, ' SET @SQL1 = @SQL1 +'DATA_TYPE ' SET @SQL1 = @SQL1 +'FROM '+ @DB +'.INFORMATION_SCHEMA.columns ' SET @SQL1 = @SQL1 +'WHERE TABLE_NAME <> ''dtproperties'' ' SET @SQL1 = @SQL1 +'AND COLLATION_NAME LIKE ''%Latin1%'' ' SET @SQL1 = @SQL1 +'ORDER BY COLUMN_NAME' EXEC (@SQL1)

END

SET @Counter = @Counter + 1

END

DROP TABLE #ctr

GO

DROP TABLE #Temp

GO