"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