How to Find Table Name Associated with Column Name: Oracle

Here is a SQL query you can use in the oracle database to search for a column name in which table it is used. So you can find the Table name.

Recently in our application, we found an error. The error displayed a column name. But we had no clue to which table it had referred. Then, we used the below query.

SQL Query: Finding table name Associated with Column Name

select owner, table_name 
from all_tab_columns 
where column_name = 'EMPNO'

How to use this query

In three steps, you will get an idea of usage. Here are the details of the steps.

1. Create two table
2. Describe both the tables
3. Use the all_tab_columns query to find the table name

Create two tables

I have created two tables — EMP1, DEP1. Below are the SQL queries to create tables.

EMP1

CREATE TABLE EMP1
(NAME CHAR(10),
EMPNO NUMBER(5),
JOIN_DATE DATE);

DEP1

CREATE TABLE DEP1
(DEP_NAME CHAR(10),
DEP_ID NUMBER(5));

Describe both tables

EMP1

ColumnNull?TypeNAME–CHAR(10)EMPNO–NUMBER(5,0)JOIN_DATE–DATE

DEP1

ColumnNull?TypeDEP_NAME–CHAR(10)DEP_ID–NUMBER(5,0

Use all_tab_columns SQL Query

Now, execute the below query to find the table. Here, I did it in the default schema. In your case, you can use the schema you want.

select owner, table_name 
from all_tab_columns 
where column_name = 'EMPNO'

The result is as follows.

OWNERTABLE_NAMESCOTTEMPSQL_BOUVYGLQZFCUYXFWCWBEDXSCMEMP1

Conclusion

To summarize, by searching for a Table in the schema using the column name, you can effectively simplify the process of bug fixing as it provides you with the Table name.