Create a UNION ALL query when the columns are in different order

Question

I have 600 tables to perform a UNION ALL query on. Unfortunately the order of the columns in each table varies, however they will always have the same name - example:

Table 1

    Item, Cost, ID, Code, Location

Table 2

    Cost, Id, Code, Location, Item

Table 3

    Id, Code, Cost, Item, Location

Is there a way to write the Union query so it will match the column names, no matter the order in the original table?

Answer 1

Alas, no. UNION ALL goes by position not by names. However, you can generate the columns:

select string_agg(column_name, ', ')
from information_schema.columns
where table_name = ? and
      table_schema = ?;

You can then plug the list into your code.

Answer 2

The following code will locate all of the tables with the specified columns in any order. It will then use the list to assemble a query that unions the data from all of the tables with the columns in the same order for each table.

declare @Query as NVarChar(max);
-- Quote column names here as needed:
declare @Prefix as NVarChar(64) = N'select Id, Item, Code, Location, Cost from ';
declare @Suffix as NVarChar(64) = NChar( 13 ) + NChar( 10 ) + N'union all' + NChar( 13 ) + NChar( 10 );

with
  TargetTables as (
    -- All of the table which have the specified list of columns, regardless of column order.
    select T.Table_Schema, T.Table_Name
      from Information_Schema.Tables as T inner join
        Information_Schema.Columns as C on C.Table_Schema = T.Table_Schema and C.Table_Name = T.Table_Name
    where C.Column_Name in ( 'Id', 'Item', 'Code', 'Location', 'Cost' ) -- Do not quote column names here.
    group by T.Table_Schema, T.Table_Name
    having Count( Distinct C.Column_Name ) = 5
    )
  -- Build the query by inserting   @Prefix   and   @Suffix   around each properly quoted table schema and name.
  select @Query = (
    select @Prefix + QuoteName( Table_Schema ) + '.' + QuoteName( Table_Name ) + @Suffix
      from TargetTables
      order by Table_Schema, Table_Name
      for XML path(''), type).value('.[1]', 'VarChar(max)' );

-- Clean up the tail end of the query.
select @Query = Stuff( @Query, DataLength( @Query ) / DataLength( N'-' ) - DataLength( @Suffix ) / DataLength( N'-' ) + 1, DataLength( @Suffix ) / DataLength( N'-' ), N';' );

-- Display the resulting query.
--   In SSMS use Results To Text (Ctrl-T) to see the query on multiple lines.
select @Query as Query;

-- Execute the query. NB: The parentheses are required.
execute ( @Query );

Depending on your needs you can run this once to get the query and cut'n'paste the resulting statement to some appropriate place, e.g. a stored procedure or view, or you can let it generate the dynamic SQL and execute it.

Additional validation, e.g. excluding system tables, is left to the reader.