FULL OUTER JOIN EXCEPT when it doesn't work blog post. Solution 2 - Except

gistfile1.sql

SELECT	'SELECT' + CHAR(9) + '[Table] = ' + QUOTENAME(t.name, CHAR(39)) + ', ' + CHAR(10) + CHAR(9) + CHAR(9) + Cols.List + CHAR(10) +
	'FROM' + CHAR(9) + @Left + '.' + QUOTENAME(t.name) + ' ' + CHAR(10) +
	'EXCEPT ' + CHAR(10) +
	'SELECT' + CHAR(9) + '[Table] = ' + QUOTENAME(t.name, CHAR(39)) + ', ' + CHAR(10) + CHAR(9) + CHAR(9) + Cols.List + CHAR(10) +
	'FROM' + CHAR(9) + @Right + '.' + QUOTENAME(t.name) + ' ' + CHAR(10)
FROM	sys.tables t
	CROSS APPLY (	SELECT	DISTINCT STUFF( (SELECT ', ' + CASE WHEN ty1.name = 'TEXT' THEN ' CAST( ' + QUOTENAME(c1.name) + ' AS VARCHAR(MAX))' ELSE QUOTENAME(c1.name) END
						FROM	sys.columns c1 
							INNER JOIN sys.types ty1
								    ON ty1.user_type_id = c1.user_type_id
						WHERE	c1.object_id = t.object_id
						ORDER BY c1.column_id
						FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)'),1,2,'') AS List
					)Cols
WHERE	t.name in (SELECT Name FROM @T)