survey-column-collations.sql

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
	DROP TABLE #tmp;

CREATE TABLE #tmp (
	[database_name] sysname NOT NULL
,	collation_name	sysname NOT NULL
,	table_type		sysname NOT NULL
,	num_tables		int		NOT NULL
,	num_columns		int		NOT NULL
,	PRIMARY KEY ([database_name], collation_name, table_type)
);

EXEC sys.sp_MSforeachdb '
USE [?];
INSERT	#tmp
SELECT	DB_NAME() AS database_name, C1.collation_name, T1.[type], COUNT(DISTINCT T1.name) AS num_tables, COUNT(*) AS num_columns
FROM	sys.columns AS C1
JOIN	sys.tables AS T1
  ON	C1.[object_id] = T1.[object_id]
WHERE	C1.collation_name IS NOT NULL
GROUP BY 
	C1.collation_name, T1.[type]
';

WITH cte AS
(
	SELECT	[database_name]
	FROM	#tmp
	GROUP BY 
		[database_name]
	HAVING COUNT(DISTINCT collation_name) > 1
)
SELECT	*
FROM	#tmp
WHERE	[database_name] IN (SELECT [database_name] FROM cte);