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

Question

Aryan Kumar · Answer

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

SQL

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 SQLPlus. Once you are connected, you can run the query by typing it into the SQLPlus prompt.

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

SQL

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.

forum

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

Can you answer this question?

1 Answers