I think you have two options. Either type out the columns in your query or do something with dynamic SQL.
declare @sql nvarchar(max)
declare @col nvarchar(max)
select @col = stuff((select ','+quotename(C.COLUMN_NAME)
from INFORMATION_SCHEMA.COLUMNS as C
where C.TABLE_NAME = 'Table2'
order by C.ORDINAL_POSITION
for xml path(''), type).value('.', 'nvarchar(max)'), 1, 1, '')
set @sql = 'select '+@col+' into Table1 from Table2 union all '+
'select '+@col+' from Table3'
exec (@sql)
Since YperSillyCube(TM) insists, I will add his answer. db2look is the tool that comes with DB2 to extract DDL from the database. You can extract things like tables, constraints, indexes, triggers, stored procedures, etc. As well as security statements, federation settings, buffer pool and tablespaces, and some database settings.
I highly recommend using it. I used to use it all the time when I worked with DB2. Very handy. Especially when combined with tools like Beyond Compare to compare DDLs between environments, etc.
The IBM Knowledge Center link is good documentation to read up on. Ember Crooks has posted two great articles on db2look here and here.
Personally, I tended to use the following options the most with the command.
db2look -d <databasename> -a -l -e -x -o <file to extract to> -td"@"
This allows me to grab several objects besides just the default, the tablespaces and bufferpools, some security settings and then make sure I set the @
sign as the statement terminator in case I have triggers, functions, or stored procedures.
You can then import those into a new database using the following db2 -td@ -vf <filename>
and then either redirect the results out or pipe to tee as I like to do on Unix. Do note that the character you use as the statement terminator in the db2look command needs to match the statement terminator supplied when importing the file.
Best Answer
Having example schema like:
and assuming you don't care about column names you can check potential conflicts in union-ed columns with query:
The result will show offending column's position in tables' structure and data type.
If you have this shortened list you can check details in information_schema.columns disctionary to check details about each column.