How to identify colums that causes a union error

Question

I am trying to UNION 25 fields in 40 tables that derives from 10 different DB's.

I received the following error when I tried to union them all together:

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

What would be the fastest way to identify which fields are not in the same format, or is there another way to UNION these tables with a "force" command like in SAS.

I am currently using SQL Server 2014.

Answer 1

Probably the fastest way is to use information_schema.columns. If, for instance, you are using select * for each table, then you can get the offending column(s) by counting the tables where columns appear:

select column_name, count(*)
from information_schema.columns
where table_schema = @schema and
      table_name in ( . . . )
group by column_name
having count(*) <> 40;

I will note that using select * for this purpose indicates a poor database design. Instead of multiple tables with the same format, you should have a single table containing all the data combined.