How to List All Tables in a Schema in Oracle Database?

In Oracle Database, listing all tables within a schema can be crucial for database management and analysis. we can use specific queries to retrieve information about tables in our schema. Below, we explore various queries to list tables, focusing on the SYSOBJECTS view that provides essential metadata about the tables in the database.

In this article, We will learn about How to List All Tables in a Schema in Oracle Database in detail.

Listing All Tables in Oracle Database Schema

Use the following query to list all tables in Oracle Database Schema.

SELECT * FROM SYSOBJECTS

This query can be modified to extract only relevant information about the tables or fetch specific types of tables. Let's cover different versions of the query, which shows how we can use use SYSOBJECTS view.

List Tables Created by User in Oracle Database

This method lists all the information regarding all the tables that are created by the user. The user clause is specified through the expression after WHERE keyword, i.e. XTYPE='U' (U stands for user).

Query:

SELECT * FROM SYSOBJECTS 
WHERE XTYPE='U';

Output:

Get Selective Information of All the Tables

This method lists only selective information regarding all the tables which are created by the user. The user clause is specified through the expression after WHERE keyword i.e. XTYPE='U' (U stands for user). Here only the name(NAME), the creation date(CRDATE) and the last reference date(REFDATE) of the table are selected.

Query:

SELECT NAME,CRDATE,REFDATE 
FROM SYSOBJECTS WHERE XTYPE='U';

Output:

Conclusion

Understanding how to list and retrieve information about tables in an Oracle Database schema is essential for effective database management. By using the SYSOBJECTS view, you can easily obtain comprehensive or selective details about the tables, aiding in tasks such as database auditing, reporting, and maintenance. Mastering these queries allows you to efficiently manage and analyze your database schema.