---
title: "How to list all tables in a schema having specific column in oracle SQL?"  
description: "How to list all tables in a schema having specific column in oracle SQL?"  
author: "Utpal Vishwas"  
published: 2023-07-17  
updated: 2023-07-18  
canonical: https://www.mindstick.com/forum/159130/how-to-list-all-tables-in-a-schema-having-specific-column-in-oracle-sql  
category: "oracle"  
tags: ["database schema", "database table", "oracle"]  
reading_time: 2 minutes  

---

# How to list all tables in a schema having specific column in oracle SQL?

How to list all [tables](https://www.mindstick.com/articles/336597/introduction-of-html-tables-for-web-development) in a [schema](https://www.mindstick.com/articles/1844/how-schema-markup-useful-in-serp) having [specific column](https://www.mindstick.com/forum/34001/how-to-get-a-specific-column-value-from-a-datatable) in [oracle SQL](https://www.mindstick.com/forum/159138/oracle-sql-casting-string-as-date-how-works)?

## Replies

### Reply by Aryan Kumar

Sure, here is how you can list all tables in a schema having specific [column](https://www.mindstick.com/forum/33860/how-to-calculate-column-summary-in-sql-server) in [Oracle](https://www.mindstick.com/articles/13016/alter-table-statement-or-command-in-oracle) [SQL](https://www.mindstick.com/articles/13115/types-of-keys-in-sql-or-oracle-database):

SQL

```plaintext
SELECT table_name
FROM user_tables
WHERE EXISTS (
    SELECT *
    FROM user_tab_cols
    WHERE table_name = user_tables.table_name
    AND column_name = 'column_name'
);
```

This query will first select all tables from the `user_tables` view. The `user_tables` view is a data dictionary view that contains information about all of the tables in the schema that the user is currently logged in to.

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

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

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

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 user_tables
WHERE EXISTS (
    SELECT *
    FROM user_tab_cols
    WHERE table_name = user_tables.table_name
    AND column_name = 'column_name'
);
```

This would return a list of all tables in the schema that the user is currently logged in to that have the specified column.


---

Original Source: https://www.mindstick.com/forum/159130/how-to-list-all-tables-in-a-schema-having-specific-column-in-oracle-sql

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