Recipe5.1.Listing Tables in a Schema

Recipe 5.1. Listing Tables in a Schema

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.

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.

Oracle's system views and DB2's system tables are each vendor-specific. PostgreSQL, MySQL, and SQL Server, on the other hand, support something called the information schema, which is a set of views defined by the ISO SQL standard. That's why the same query can work for all three of those databases.