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

Prerequisite: Create Database in MS SQL Server

There are multiple ways to list all the tables present in a Schema in Oracle SQL. Such ways are depicted in the below article. For this article, we will be using the Microsoft SQL Server as our database. 

Method 1: This method lists all the 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).

Query:

SELECT * FROM SYSOBJECTS 
WHERE XTYPE='U';

Output:

Method 2: 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:

Method 3: This method lists all the information regarding all the tables. Here, since we have not specified the XTYPE to USER, the query shall display all the tables irrespective of their creators.

Query:

SELECT * FROM SYSOBJECTS;

Output:

Method 4: This method lists only selective information regarding all the tables. Here, since we have not specified the XTYPE to USER, the query shall display all the tables irrespective of their creators. 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;

Output: