How to Search Oracle Database for 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.

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

Column	Null?	Type
NAME	–	CHAR(10)
EMPNO	–	NUMBER(5,0)
JOIN_DATE	–	DATE

DEP1

Column	Null?	Type
DEP_NAME	–	CHAR(10)
DEP_ID	–	NUMBER(5,0

Use the 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. The EMP1 is the table where it mentioned the column EMPNO.

OWNER	TABLE_NAME
SCOTT	EMP
SQL_BOUVYGLQZFCUYXFWCWBEDXSCM	EMP1

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.

Related