Finding common column names in tables for UNION queries

Sometimes if you have tables with a lot of columns which you are trying to union, it can be hard to identify the columns they have in common and you may get this error a lot

Msg 205, Level 16, State 1, Line 1

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

 One of the nice things you can do using the INFORMATION_SCHEMA views in SQL Server 2008+ is to query the schema to find the columns in common

SELECT  COLUMN_NAME
FROM    INFORMATION_SCHEMA.COLUMNS H
WHERE   TABLE_NAME = ''
        AND EXISTS ( SELECT 1
                     FROM   INFORMATION_SCHEMA.COLUMNS W
                     WHERE  TABLE_NAME = ''
                            AND W.COLUMN_NAME = H.COLUMN_NAME )

 This will return the column names that your two tables/views have in common with output like below

You can then use this output to generate a select list for your UNION queries