How to compare row with a column?

Question

create table TABLE1(
column1 varchar (100),
column2 varchar (100)
)
go
create table TABLE2(
column_for_compare varchar (100)
)
go
insert into TABLE2 values ('column1')
insert into TABLE2 values ('column2')
go

I want to check if TABLE1 columns name exists in TABLE2 ,something like this: --This should be comparing the row values in TABLE2 with column names in TABLE1

if exists(select column_for_compare from TABLE2) and if exists(select column_name from syscolumn key join systable where table_name ='TABLE1') begin print 'match found' end

i hope you can see what im trying...if not...if will try to explain it better

Answer 1

You can query the USER_TAB_COLUMNS table to get the collumn names

SELECT column_name
FROM USER_TAB_COLUMNS
WHERE table_name = 'TABLE1'

and then join the result with TABLE2

SELECT *
FROM TABLE2, (SELECT column_name
                FROM USER_TAB_COLUMNS
                WHERE table_name = 'TABLE1') as cNames
WHERE TABLE2.column_for_compare=cNames.column_name

This will get you a table with the collumn names in table_1 that exist in the rows in table_2