---
title: "How to Search Oracle Database for Column"
description: "Here is a helpful SQL query to find a Table name in the database for a column. Useful for error fixing or bug fixing."
url: https://srinimf.com/2022/03/12/how-to-find-table-name-for-a-column-search-database/
date: 2022-03-12
modified: 2026-10-10
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2022/03/pexels-photo-4050304.jpeg?fit=1880%2C1253&ssl=1
categories: ["Oracle"]
tags: ["SQL to find table name for a column"]
type: post
lang: en
---

# How to Search Oracle Database for Column

Here is a [SQL query](https://srinimf.com/2010/07/04/db2-row-number/) 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.

*Search Oracle database*

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](https://i0.wp.com/srinimf.com/wp-content/uploads/2022/03/pexels-photo-327483.jpeg?resize=1733%2C1300&ssl=1)

## **Finding Table name for a Specific 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](https://srinimf.com/2022/03/05/how-to-get-date-only-from-datetime-column/) 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](https://srinimf.com/2016/09/08/2-simple-sql-queries-on-self-join-and-inner-join/) 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](https://srinimf.com/2016/01/14/cobol-date-logic-to-find-number-of-days/) 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

- [DB2: How to Fix Column MASK Error When You Use GROUP BY](https://srinimf.com/2016/02/04/db2-column-access-control-with-group-by/)DateFebruary 4, 2016
- [How to RENAME Table: Here is an Example SQL Query](https://srinimf.com/2023/10/14/rename-in-sql-how-it-works-in-two-popular-databases/)DateOctober 14, 2023
- [How to Create Unique Identifier: Oracle SQL](https://srinimf.com/2023/11/29/sql-sequence-writing/)DateNovember 29, 2023

### Share this:

-
- [Tweet](https://twitter.com/share)
- [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2022%2F03%2F12%2Fhow-to-find-table-name-for-a-column-search-database%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2022%2F03%2Fpexels-photo-4050304.jpeg%3Ffit%3D1200%252C800%26ssl%3D1&description=How%20to%20Search%20Oracle%20Database%20for%20Column)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2022/03/12/how-to-find-table-name-for-a-column-search-database/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2022/03/12/how-to-find-table-name-for-a-column-search-database/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2022/03/12/how-to-find-table-name-for-a-column-search-database/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2022/03/12/how-to-find-table-name-for-a-column-search-database/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20How%20to%20Search%20Oracle%20Database%20for%20Column&body=https%3A%2F%2Fsrinimf.com%2F2022%2F03%2F12%2Fhow-to-find-table-name-for-a-column-search-database%2F&share=email)
-

### Like this:

Like Loading…