CODEWITHSUNDEEP
sqloracle-databasedb2
Vamsi Emani
Vamsi Emani

Reputation: 10302

Search and get tablename which has a given column name?

Can I write an sql query that can find the names of all tables in a database that have column name like %COL_FAX_ID%. If so, how?

Database used is oracle or db2.

Upvotes: 1

Views: 12076

Answers (6)

bhamby
bhamby

Reputation: 15450

For DB2, you will want to use the SYSCAT.COLUMNS catalog view.

SELECT *
FROM SYSCAT.COLUMNS
WHERE COLNAME LIKE '%COL_FAX_ID%'

Upvotes: 0

valex
valex

Reputation: 24144

For oracle:

SELECT table_name,column_name from all_tab_columns 
where column_name like '%COL_FAX_ID%'

Upvotes: 0

user462356
user462356

Reputation:

I don't have an oracle install lying around to test this with but you should be able to do something like:

SELECT TABLE_NAME
FROM ALL_TAB_COLUMNS
WHERE COLUMN_NAME LIKE '%COL_FAX_ID%'

Upvotes: 2

Alexander Tokarev
Alexander Tokarev

Reputation: 1035

select distinct table_name from all_tab_columns where column_name like %'COL_FAX_ID%'

Upvotes: 0

Ollie
Ollie

Reputation: 17538

For Oracle, you could try:

SELECT owner,
       table_name,
       column_name
  FROM all_tab_cols
 WHERE column_name LIKE '%COL_FAX_ID%'
 ORDER BY owner,
          table_name;

For a full list of the Oracle data dictionary views etc. see here.

Hope it helps...

Upvotes: 6

Breezer
Breezer

Reputation: 10490

Dublicate of possible How to find all the tables in MySQL with specific column names in them?

Answer:

SELECT DISTINCT TABLE_NAME 
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME IN ('columnA','ColumnB')
        AND TABLE_SCHEMA='YourDatabase';

Edit: Sorry I Miss saw the sql for beeing mysql tag... But this may work aswell? Dunno. Gl & Hf

Upvotes: 0

Related Questions