How to get list of table names in different schema of an Oracle database

Question

Aryan Kumar · Answer

Sure, here is how you can get the list of tables names in different schemas of an Oracle database:

SQL

SELECT table_name
FROM all_tables
WHERE owner IN ('schema_name1', 'schema_name2', 'schema_name3');

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 IN operator to check if the owner of each table in the all_tables view is equal to one of the specified schemas. The IN operator takes a list of values as its argument. In this case, the list of values is the list of schemas that you want to list the tables for.

If the IN operator returns TRUE for a table, then the table in the all_tables view belongs to one of the specified schemas.

The final part of the query selects the table_name column from the all_tables view for all tables that belong to one of the specified schemas.

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 owner IN ('schema_name1', 'schema_name2', 'schema_name3');

This would return a list of all tables in the specified schemas.

forum

How to get list of table names in different schema of an Oracle database

Can you answer this question?

1 Answers