How to check if a same column exists in various tables

Database
Enthusiast

How to check if a same column exists in various tables

I have a situation where I need to join any common column (not provided to me) in table1 & table2 so that I can access column1 & column2 of table2 while joining both tables.

I need to find out common column(s) between table1 & table2 from dbc.tables or dbc.columns.

Kindly assist.

3 REPLIES
Enthusiast

Re: How to check if a same column exists in various tables

I tried the below but didn't get anything:

select

a.columnname

from

dbc.columns a, dbc.columns b

where

a.columnname = b.columnname

and

( (a.databasename = 'dbase1' and a.tablename = 'table1') AND (b.databasename = 'dbase2' and a.tablename = 'table2') );

Enthusiast

Re: How to check if a same column exists in various tables

in the second condition use b.tablename='table2' instead of a.tablename='table2'.

However it might not always be tru that the column names are the same across the tables. you should have the datamodel to see the linking between the tables.

Enthusiast

Re: How to check if a same column exists in various tables

Thanks. This realizes what a small displacement can do.

Thanks, Actually I am in a downstream data team that doesn't have access to the data model and other basic documents. So, the need to invent such query.