Search and get tablename which has a given column name?

Question

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.

score 6 · Answer 1 · answered Aug 22 '12 at 08:27

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...

score 2 · Answer 2 · answered Aug 22 '12 at 08:28

2

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%'

answered Aug 22 '12 at 08:28

1

I'd use ALL_TAB_COLS rather than ALL_TAB_COLUMNS as per the Oracle definition: This view (ALL_TAB_COLS) differs from "ALL_TAB_COLUMNS" in that hidden columns are not filtered out – Ollie Aug 22 '12 at 08:31

score 0 · Answer 3 · edited May 23 '17 at 12:22

0

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

edited May 23 '17 at 12:22

Community

1
1

answered Aug 22 '12 at 08:26

Breezer

10,410
6
29
50

score 0 · Answer 4 · answered Aug 22 '12 at 08:28

0

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

answered Aug 22 '12 at 08:28

Alexander Tokarev

1,000
7
16

I'd use `ALL_TAB_COLS` rather than `ALL_TAB_COLUMNS` as per the Oracle definition: This view (ALL_TAB_COLS) differs from "ALL_TAB_COLUMNS" in that hidden columns are not filtered out. – Ollie Aug 22 '12 at 08:30
1

Regarding the author would like to get table name all_tab_columns is fine as well but many thanks at any case – Alexander Tokarev Aug 22 '12 at 09:06

score 0 · Answer 5 · answered Aug 22 '12 at 08:31

0

For oracle:

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

answered Aug 22 '12 at 08:31

valex

23,966
7
43
60

score 0 · Answer 6 · answered Aug 22 '12 at 15:33

0

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

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

answered Aug 22 '12 at 15:33

bhamby

15,112
1
45
66

Search and get tablename which has a given column name?

6 Answers6