• "Most DBAs don't ever deal with multiple languages or different collation and sort order settings in SQL Server"

    The majority of UK DBA's will have come across collation issues, its a common problem for me. I currently have a server having 9 databases with various different collations at table and row level and am trying to clean up the mess.... To identify whether I had issues:-

    USE MASTER

    GO

    SET NOCOUNT ON

    DECLARE @DB VARCHAR (150),

    @Counter INT,

    @Rec VARCHAR (150),

    @SQL VARCHAR (1000),

    @SQL1 VARCHAR (1000),

    @SQL2VARCHAR (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 more than one*/

    CREATE TABLE #ctr

    ( NumRows int )

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

    BEGIN

    SET @DB = (SELECT name

    FROM #Temp

    WHERE database_id = @Counter)

    --Alter 'Latin' below if not just comparing US/UK

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

    FROM '+ @DB +'.INFORMATION_SCHEMA.columns

    WHERE COLLATION_NAME LIKE ''%Latin%'' '

    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 ''%Latin%'' '

    SET @SQL1 = @SQL1 +'ORDER BY COLUMN_NAME'

    EXEC (@SQL1)

    END

    SET @Counter = @Counter + 1

    END

    DROP TABLE #ctr

    GO

    DROP TABLE #Temp

    GO

    Facts are stubborn things, but statistics are more pliable - Mark Twain
    Carolyn
    SQLServerSpecialists[/url]