Listing Tables in a Database

Before you can do anything in a database, you will need to know what tables exist. One of the requirements for a database management system to be able to call itself relational is that it must support a data dictionary (also known as a system catalog) which can be queried to discover details of the database structure.

There is a "standard" data dictionary, known as the INFORMATION_SCHEMA, but this is not globally implemented (Oracle, for one, does not support it) and it also shows limited data (it does not show information about extended database-specific attributes, since it aims to be database independent). If you have access to the INFORMATION_SCHEMA, you can get a list of the tables by running:

SELECT *
FROM information_schema.tables

If you do not want to access the standard data dictionary, or it is not available in your environment, then there are other queries that can be run to get the same information.

Oracle

In Oracle, there is an extensive data dictionary. For the majority of the views in the data dictionary, there are 3 variants:

• USER_%
  which show details of items that you own based on who you are logged in as,
• ALL_%
  which show details of items that you can access, and
• DBA_%
  which show details of all items in the database, whether you can access them or not.

To find which tables you own, you could run:

SELECT *
FROM user_tables;

MySQL

MySQL supports a simplified syntax to get a list of the available tables:

show tables;

PostgreSQL

The PostgreSQL data dictionary includes a number of tables with the prefix pg_ which can be used to get useful information about the database. To list the tables in a PostgreSQL database, execute:

SELECT *
FROM pg_tables;

SQLite

SQLite exposes the data dictionary information through the sqlite_master table. To find what tables exist in your SQLite database, you just need to connect and execute:

SELECT *
FROM sqlite_master
WHERE type='table';