sql - Select unique column names from a list of joined tables -

i have list of tables can joined same pk column. since list of tables can vary project project, want create query can dynamic enough pull unique columns these tables.

for example, have 3 tables below: table (pk field, column1, column 2) table b (pk field, column3, column 4) table c (pk field, column5, column 5) 

these 3 tables joined on "pk field" column, , want query output like:

pk field  column1  column2  column3  column4  column5 ..data..  ..data.. ..data.. ..data.. ..data.. ..data.. 

at end, query part of sql function or sp, user can define list of tables, , pk field @ beginning, executing shall return expected output dataset.

i think use query below result not like:

select column_name information_schema.columns table_name = '' 

any advice how should design sp or function, appreciated.

thanks in advance.

ddl 2 example tables:

create table [dbo].[g_bdem](     [blaisekey_code] [nvarchar](255) null,     [qsex] [int] null,     [qdob] [datetime] null,     [qdobnr] [int] null,     [qage] [int] null,     [qagenr] [int] null,     [qagerange] [int] null,     [qage15orover] [int] null,     [qnoteligible] [nvarchar](1) null,     [qborninnz] [int] null,     [qcountryofbirth] [nvarchar](2) null,     [qarrivenzyr] [int] null,     [qarrivenzyrnr] [int] null,     [qarrivenzmth] [int] null,     [bdem_bop_qhowmanyraised] [int] null,     [bdem_bop_q1stparentbornnz] [int] null,     [bdem_bop_q2ndparentbornnz] [int] null,     [bdem_bop_qhowmanyparentbornnz] [int] null,     [qmaoridescent] [int] null,     [qschqual] [int] null,     [qschqualoth] [nvarchar](200) null,     [qschqualothnr] [int] null,     [qschqualyr] [int] null,     [qschqualyrnr] [int] null,     [qpostschqual] [int] null,     [q3mthsstudy] [int] null,     [qhighestqual] [int] null,     [qhighestqualoth] [nvarchar](200) null,     [qhighestqualothnr] [int] null,     [qhighestqualyr] [int] null,     [qhighestqualyrnr] [int] null,     [qworkintro] [nvarchar](1) null,     [qdidpaidwork] [int] null,     [qawayfromwork] [int] null,     [qfamilybuswork] [int] null,     [bdem_wor_qpaidworkintro] [nvarchar](1) null,     [bdem_wor_qjobsnum] [int] null,     [bdem_wor_qjobsnumnr] [int] null,     [bdem_wor_tabdem_t2_ftotmins] [int] null,     [bdem_wor_q2jobsnohrsintro] [nvarchar](1) null,     [bdem_wor_q2jobs2hrsintro] [nvarchar](1) null,     [bdem_wor_q2jobs1hrsintro] [nvarchar](1) null,     [bdem_wor_qoccupation] [nvarchar](200) null,     [bdem_wor_qoccupationnr] [int] null,     [bdem_wor_qmaintasks] [nvarchar](200) null,     [bdem_wor_qmaintasksnr] [int] null,     [bdem_wor_qfeelaboutjob] [int] null,     [bdem_wor_qemployarrangement] [int] null,     [bdem_wor_qpermemployee] [int] null,     [qhasjobtostart] [int] null,     [qlookedforwork] [int] null,     [qjobsearcha] [int] null,     [qjobsearchb] [int] null,     [qjobsearchc] [int] null,     [qjobsearchd] [int] null,     [qjobsearche] [int] null,     [qjobsearchf] [int] null,     [qjobsearchg] [int] null,     [qjobsearchh] [int] null,     [qjobsearchi] [int] null,     [qjobsearchoth] [nvarchar](200) null,         [qjobsearchothnr] [int] null,     [qcouldstartlastwk] [int] null,     [qinctotalamt] [int] null,     [fcountryname] [nvarchar](60) null      ) on [primary]      go  create table [dbo].[g_blww](     [blaisekey_code] [nvarchar](255) null,     [qthingsworthwhilescale] [int] null  ) on [primary] 

this script generate dynamic sql table similar pk name.

query:

set nocount on  if object_id (n'dbo.a') not null    drop table dbo.a  if object_id (n'dbo.b') not null    drop table dbo.b  if object_id (n'dbo.c') not null    drop table dbo.c  create table dbo.a (pk_field int primary key, column1 int, column2 int) create table dbo.b (pk_field int primary key, column3 int, column4 int) create table dbo.c (pk_field int primary key, column5 int, [column 6] int)  insert dbo.a (pk_field, column1, column2) values (1, 1, 2), (2, 1, 2)   insert dbo.b (pk_field, column3, column4) values (2, 3, 4)   insert dbo.c (pk_field, column5, [column 6]) values (1, 5, 6), (3, 5, 6)   declare @sql nvarchar(max)  ;with cte  (     select            column_name = '[' + c.name + ']'         , table_name = '[' + s.name + '].[' + o.name + ']'     sys.columns c (nolock)     join sys.objects o (nolock) on c.[object_id] = o.[object_id]     join sys.schemas s (nolock) on o.[schema_id] = s.[schema_id]     o.name in ('a', 'b', 'c')         , s.name = 'dbo'         , o.[type] = 'u'   ), unicol (     select top 1 column_name      cte      group cte.column_name     having count(cte.column_name) > 1 ), cols  (     select distinct column_name      cte     ), tbl  (     select distinct table_name     cte ), rs  (     select            tbl.table_name         , column_name = isnull(cte.column_name, cols.column_name + ' = null')     cols     cross join tbl     left join cte on cols.column_name = cte.column_name , cte.table_name = tbl.table_name ), rs2 (     select uni = ' union all' + char(13) + 'select ' + stuff((         select ', ' + rs.column_name         rs         tbl.table_name = rs.table_name         group rs.column_name         order rs.column_name         xml path(''), type).value('.', 'nvarchar(max)'), 1, 2, '') +          ' ' + table_name     tbl )  select @sql = 'select  ' + stuff((     select char(13) + ', ' + isnull(unicol.column_name, cols.column_name + ' = max(' + cols.column_name + ')')     cols     left join unicol on cols.column_name = unicol.column_name     xml path(''), type).value('.', 'nvarchar(max)'), 1, 2, ' ')  + '   (' + stuff((     select char(10) + uni     rs2     xml path(''), type).value('.', 'nvarchar(max)'), 1, 11, '') + char(13) +      ') t  group ' + (select column_name unicol)  print @sql  execute sys.sp_executesql @sql 

output:

select        [column 6] = max([column 6])     , [column1] = max([column1])     , [column2] = max([column2])     , [column3] = max([column3])     , [column4] = max([column4])     , [column5] = max([column5])     , [pk_field]  (     select [column 6] = null, [column1], [column2], [column3] = null, [column4] = null, [column5] = null, [pk_field] [dbo].[a]      union     select [column 6] = null, [column1] = null, [column2] = null, [column3], [column4], [column5] = null, [pk_field] [dbo].[b]      union     select [column 6], [column1] = null, [column2] = null, [column3] = null, [column4] = null, [column5], [pk_field] [dbo].[c] ) t  group [pk_field] 

results:

column 6    column1     column2     column3     column4     column5     pk_field ----------- ----------- ----------- ----------- ----------- ----------- ----------- 6           1           2           null        null        5           1 null        1           2           3           4           null        2 6           null        null        null        null        5           3 

update in script:

declare @sql nvarchar(2000) -> nvarchar(max) 

output ddl:

select    [blaisekey_code] , [bdem_bop_q1stparentbornnz] = max([bdem_bop_q1stparentbornnz]) , [bdem_bop_q2ndparentbornnz] = max([bdem_bop_q2ndparentbornnz]) , [bdem_bop_qhowmanyparentbornnz] = max([bdem_bop_qhowmanyparentbornnz]) , [bdem_bop_qhowmanyraised] = max([bdem_bop_qhowmanyraised]) , [bdem_wor_q2jobs1hrsintro] = max([bdem_wor_q2jobs1hrsintro]) , [bdem_wor_q2jobs2hrsintro] = max([bdem_wor_q2jobs2hrsintro]) , [bdem_wor_q2jobsnohrsintro] = max([bdem_wor_q2jobsnohrsintro]) , [bdem_wor_qemployarrangement] = max([bdem_wor_qemployarrangement]) , [bdem_wor_qfeelaboutjob] = max([bdem_wor_qfeelaboutjob]) , [bdem_wor_qjobsnum] = max([bdem_wor_qjobsnum]) , [bdem_wor_qjobsnumnr] = max([bdem_wor_qjobsnumnr]) , [bdem_wor_qmaintasks] = max([bdem_wor_qmaintasks]) , [bdem_wor_qmaintasksnr] = max([bdem_wor_qmaintasksnr]) , [bdem_wor_qoccupation] = max([bdem_wor_qoccupation]) , [bdem_wor_qoccupationnr] = max([bdem_wor_qoccupationnr]) , [bdem_wor_qpaidworkintro] = max([bdem_wor_qpaidworkintro]) , [bdem_wor_qpermemployee] = max([bdem_wor_qpermemployee]) , [bdem_wor_tabdem_t2_ftotmins] = max([bdem_wor_tabdem_t2_ftotmins]) , [fcountryname] = max([fcountryname]) , [q3mthsstudy] = max([q3mthsstudy]) , [qage] = max([qage]) , [qage15orover] = max([qage15orover]) , [qagenr] = max([qagenr]) , [qagerange] = max([qagerange]) , [qarrivenzmth] = max([qarrivenzmth]) , [qarrivenzyr] = max([qarrivenzyr]) , [qarrivenzyrnr] = max([qarrivenzyrnr]) , [qawayfromwork] = max([qawayfromwork]) , [qborninnz] = max([qborninnz]) , [qcouldstartlastwk] = max([qcouldstartlastwk]) , [qcountryofbirth] = max([qcountryofbirth]) , [qdidpaidwork] = max([qdidpaidwork]) , [qdob] = max([qdob]) , [qdobnr] = max([qdobnr]) , [qfamilybuswork] = max([qfamilybuswork]) , [qhasjobtostart] = max([qhasjobtostart]) , [qhighestqual] = max([qhighestqual]) , [qhighestqualoth] = max([qhighestqualoth]) , [qhighestqualothnr] = max([qhighestqualothnr]) , [qhighestqualyr] = max([qhighestqualyr]) , [qhighestqualyrnr] = max([qhighestqualyrnr]) , [qinctotalamt] = max([qinctotalamt]) , [qjobsearcha] = max([qjobsearcha]) , [qjobsearchb] = max([qjobsearchb]) , [qjobsearchc] = max([qjobsearchc]) , [qjobsearchd] = max([qjobsearchd]) , [qjobsearche] = max([qjobsearche]) , [qjobsearchf] = max([qjobsearchf]) , [qjobsearchg] = max([qjobsearchg]) , [qjobsearchh] = max([qjobsearchh]) , [qjobsearchi] = max([qjobsearchi]) , [qjobsearchoth] = max([qjobsearchoth]) , [qjobsearchothnr] = max([qjobsearchothnr]) , [qlookedforwork] = max([qlookedforwork]) , [qmaoridescent] = max([qmaoridescent]) , [qnoteligible] = max([qnoteligible]) , [qpostschqual] = max([qpostschqual]) , [qschqual] = max([qschqual]) , [qschqualoth] = max([qschqualoth]) , [qschqualothnr] = max([qschqualothnr]) , [qschqualyr] = max([qschqualyr]) , [qschqualyrnr] = max([qschqualyrnr]) , [qsex] = max([qsex]) , [qthingsworthwhilescale] = max([qthingsworthwhilescale]) , [qworkintro] = max([qworkintro])   ( select [bdem_bop_q1stparentbornnz], [bdem_bop_q2ndparentbornnz], [bdem_bop_qhowmanyparentbornnz], [bdem_bop_qhowmanyraised], [bdem_wor_q2jobs1hrsintro], [bdem_wor_q2jobs2hrsintro], [bdem_wor_q2jobsnohrsintro], [bdem_wor_qemployarrangement], [bdem_wor_qfeelaboutjob], [bdem_wor_qjobsnum], [bdem_wor_qjobsnumnr], [bdem_wor_qmaintasks], [bdem_wor_qmaintasksnr], [bdem_wor_qoccupation], [bdem_wor_qoccupationnr], [bdem_wor_qpaidworkintro], [bdem_wor_qpermemployee], [bdem_wor_tabdem_t2_ftotmins], [blaisekey_code], [fcountryname], [q3mthsstudy], [qage], [qage15orover], [qagenr], [qagerange], [qarrivenzmth], [qarrivenzyr], [qarrivenzyrnr], [qawayfromwork], [qborninnz], [qcouldstartlastwk], [qcountryofbirth], [qdidpaidwork], [qdob], [qdobnr], [qfamilybuswork], [qhasjobtostart], [qhighestqual], [qhighestqualoth], [qhighestqualothnr], [qhighestqualyr], [qhighestqualyrnr], [qinctotalamt], [qjobsearcha], [qjobsearchb], [qjobsearchc], [qjobsearchd], [qjobsearche], [qjobsearchf], [qjobsearchg], [qjobsearchh], [qjobsearchi], [qjobsearchoth], [qjobsearchothnr], [qlookedforwork], [qmaoridescent], [qnoteligible], [qpostschqual], [qschqual], [qschqualoth], [qschqualothnr], [qschqualyr], [qschqualyrnr], [qsex], [qthingsworthwhilescale] = null, [qworkintro] [dbo].[g_bdem]  union select [bdem_bop_q1stparentbornnz] = null, [bdem_bop_q2ndparentbornnz] = null, [bdem_bop_qhowmanyparentbornnz] = null, [bdem_bop_qhowmanyraised] = null, [bdem_wor_q2jobs1hrsintro] = null, [bdem_wor_q2jobs2hrsintro] = null, [bdem_wor_q2jobsnohrsintro] = null, [bdem_wor_qemployarrangement] = null, [bdem_wor_qfeelaboutjob] = null, [bdem_wor_qjobsnum] = null, [bdem_wor_qjobsnumnr] = null, [bdem_wor_qmaintasks] = null, [bdem_wor_qmaintasksnr] = null, [bdem_wor_qoccupation] = null, [bdem_wor_qoccupationnr] = null, [bdem_wor_qpaidworkintro] = null, [bdem_wor_qpermemployee] = null, [bdem_wor_tabdem_t2_ftotmins] = null, [blaisekey_code], [fcountryname] = null, [q3mthsstudy] = null, [qage] = null, [qage15orover] = null, [qagenr] = null, [qagerange] = null, [qarrivenzmth] = null, [qarrivenzyr] = null, [qarrivenzyrnr] = null, [qawayfromwork] = null, [qborninnz] = null, [qcouldstartlastwk] = null, [qcountryofbirth] = null, [qdidpaidwork] = null, [qdob] = null, [qdobnr] = null, [qfamilybuswork] = null, [qhasjobtostart] = null, [qhighestqual] = null, [qhighestqualoth] = null, [qhighestqualothnr] = null, [qhighestqualyr] = null, [qhighestqualyrnr] = null, [qinctotalamt] = null, [qjobsearcha] = null, [qjobsearchb] = null, [qjobsearchc] = null, [qjobsearchd] = null, [qjobsearche] = null, [qjobsearchf] = null, [qjobsearchg] = null, [qjobsearchh] = null, [qjobsearchi] = null, [qjobsearchoth] = null, [qjobsearchothnr] = null, [qlookedforwork] = null, [qmaoridescent] = null, [qnoteligible] = null, [qpostschqual] = null, [qschqual] = null, [qschqualoth] = null, [qschqualothnr] = null, [qschqualyr] = null, [qschqualyrnr] = null, [qsex] = null, [qthingsworthwhilescale], [qworkintro] = null [dbo].[g_blww] ) t  group [blaisekey_code]