Listing Tables in a Schema - SQL

Problem

You want to see a list all the tables you've created in a given schema.

Solution

The solutions that follow all assume you are working with the SMEAGOL schema. The basic approach to a solution is the same for all RDBMSs: you query a system table (or view) containing a row for each table in the database.

DB2

Query SYSCAT.TABLES:

 1 select tabname
 2   from syscat.tables
 3  where tabschema = 'SMEAGOL'

Oracle

Query SYS.ALL_TABLES:

 select table_name
   from all_tables
  where owner = 'SMEAGOL'

PostgreSQL, MySQL, and SQL Server

Query INFORMATION_SCHEMA.TABLES:

 1 select table_name
 2   from information_schema.tables
 3  where table_schema = 'SMEAGOL'

Discussion

In a delightfully circular manner, databases expose information about themselves through the very mechanisms that you create for your own applications: tables and views. Oracle, for example, maintains an extensive catalog of system views, such as ALL_TABLES, that you can query for information about tables, indexes, grants, and any other database object.

TIPS :

Oracle's catalog views are just that, views. They are based on an underlying set of tables that contain the information in a very user-unfriendly form. The views put a very usable face on Oracle's catalog data.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table