How to Find Table name for a Column

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.

How to Find Table name for a Column

SQL Query: Finding table name for a column in Oracle

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

In conclusion, you can search for any column in the schema. So you will get the Table name. It simplifies bug fixing since you know the Table name.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store