FULL OUTER JOIN EXCEPT when it doesn't work blog post. Solution 1 - the full outer join

gistfile1.sql

SELECT	'SELECT ' +
    	'CASE WHEN ' + Ord.Predicate + ' != ' + cast(t.max_column_id_used as varchar(50)) + ' THEN 1 ELSE 0 END AS [NoMatch], ' +				                -- identify where left and right columns do not join 
    	'* ' +																																					-- select *
    	'FROM ' + @Left +'.'+ QUOTENAME(t.name) + ' src ' +																										-- from T1
    	'FULL OUTER JOIN ' + @Right + '.' + QUOTENAME(t.name) + ' tgt ' +																						-- join T2
    	' ON COALESCE(tgt.' + QUOTENAME(c.name) + ', ' + tyd.Value + ') = COALESCE(src.' + QUOTENAME(c.name) + ', ' + TYD.Value + ') ' +			            -- If joining on a null use a default type value
    	Cols.Predicate + ' ' +																																	-- the rest of teh join syntax. If joining on a null use a default type value
    	'WHERE CASE WHEN ' + Ord.Predicate + ' != ' + cast(t.max_column_id_used as varchar(50)) + ' THEN 1 ELSE 0 END = 1 ' +					                -- exclude rows where there is a match
    	'ORDER BY CASE WHEN ' + Ord.Predicate + ' != ' + cast(t.max_column_id_used as varchar(50)) + ' THEN 1 ELSE 0 END, src.' + c.name + ', tgt.' + c.name	-- Order by NoMatch, then T1.Id, T2.Id
FROM	sys.tables t
	    INNER JOIN sys.columns c
	            ON c.object_id = t.object_id
	    	    AND c.column_id = 1
    	INNER JOIN sys.types ty
    		    ON ty.user_type_id = c.user_type_id
    	INNER JOIN @Type tyd
    		    ON ty.name = tyd.Name
    	CROSS APPLY (	SELECT	DISTINCT STUFF( (   SELECT 'AND COALESCE(tgt.' + QUOTENAME(c1.name) + ',''' + tyd1.Value + ''') = COALESCE(src.' + QUOTENAME(c1.name) + ',''' + tyd1.Value + ''') '
    						                        FROM	sys.columns c1 
                                							INNER JOIN sys.types ty1
                                								ON ty1.user_type_id = c1.user_type_id
                                							INNER JOIN @Type tyd1
                                								on ty1.name = tyd1.Name
                                					WHERE	c1.object_id = t.object_id
                                							AND c1.column_id > 1
                                					ORDER BY c1.column_id
                                					FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)'),1,0,'') AS Predicate
    				)Cols
    	CROSS APPLY (	SELECT	DISTINCT STUFF( (SELECT  '+ CASE WHEN COALESCE(tgt.' + QUOTENAME(c2.name) + ',''' + tyd2.Value + ''') = COALESCE(src.' + QUOTENAME(c2.name) + ',''' + tyd2.Value + ''') THEN 1 ELSE 0 END '
    					FROM	sys.columns c2
    							INNER JOIN sys.types ty2
    								ON ty2.user_type_id = c2.user_type_id
    							INNER JOIN @Type tyd2
    								on ty2.name = tyd2.Name
    					WHERE	c2.object_id = t.object_id
    					ORDER BY c2.column_id
    					FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)'),1,1,'') AS Predicate
    				)Ord
WHERE	t.name in (SELECT Name FROM @T)
;