---
title: "How to find all tables in Oracle that have specific columns?"  
description: "How to find all tables in Oracle that have specific columns?"  
author: "Utpal Vishwas"  
published: 2023-07-17  
updated: 2023-07-18  
canonical: https://www.mindstick.com/forum/159131/how-to-find-all-tables-in-oracle-that-have-specific-columns  
category: "oracle"  
tags: ["database", "database table", "oracle"]  
reading_time: 2 minutes  

---

# How to find all tables in Oracle that have specific columns?

How to find all [tables](https://www.mindstick.com/articles/336597/introduction-of-html-tables-for-web-development) in [Oracle](https://www.mindstick.com/articles/13016/alter-table-statement-or-command-in-oracle) that have specific columns?

## Replies

### Reply by Aryan Kumar

To find all tables in Oracle that have specific columns, you can use the following SQL query:

SQL

```plaintext
SELECT table_name
FROM all_tables
WHERE EXISTS (
    SELECT *
    FROM all_tab_cols
    WHERE table_name = all_tables.table_name
    AND column_name IN ('column1', 'column2', 'column3')
);
```

This query will first select all tables from the `all_tables` view. The `all_tables` view is a data dictionary view that contains information about all of the tables in the database.

The next part of the query uses the `EXISTS` clause to check if each table in the `all_tables` view has the specified columns. The `EXISTS` clause takes a subquery as its argument. The subquery in this case selects all columns from the `all_tab_cols` view where the table name is equal to the table name in the `all_tables` view and the column name is equal to one of the specified columns.

If the subquery returns any rows, then the `EXISTS` clause will return `TRUE`. This means that the table in the `all_tables` view has the specified columns.

The final part of the query selects the `table_name` column from the `all_tables` view for all tables that have the specified columns.

To run this query, you will need to connect to the Oracle database using a tool like SQL*Plus. Once you are connected, you can run the query by typing it into the SQL*Plus prompt.

For example, to run the query above, you would type the following into the SQL*Plus prompt:

SQL

```plaintext
SELECT table_name
FROM all_tables
WHERE EXISTS (
    SELECT *
    FROM all_tab_cols
    WHERE table_name = all_tables.table_name
    AND column_name IN ('column1', 'column2', 'column3')
);
```

This would return a list of all tables in the database that have the specified columns.


---

Original Source: https://www.mindstick.com/forum/159131/how-to-find-all-tables-in-oracle-that-have-specific-columns

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.