Retrieving a List of Tables using SQL
Information (table names) is stored in as metadata of the database schema. Each database vendor stores the information about their schema in different ways. Therefore, to be able to get a list of table names in SQL, knowledge about the system tables of the database being used is required.
The following examples illustrate how to get a list of table names in SQL, specific to each database vendor:
SELECT * FROM all_tables;
SELECT * FROM user_tables;
Both ‘all_tables’ and ‘user_tables’ are system tables in Oracle. The first SQL statement will get a list of all the table names, whereas the second command will only show the user-created tables, by filtering out the system tables altogether. The SELECT command is used with the wildcard ‘*’ to indicate all the records should be selected.
In MySQL, there is a specific command to retrieve table names. The full syntax of the command allows filters to be applied to the results, which eliminates various records from the results.
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
In MSSQL, the meta data is stored in the INFORMATION_SCHEMA. To retrieve information about tables in particular, the stored procedure TABLES is invoked on the INFORMATION_SCHEMA. An additional qualifier of TABLE_TYPE is applied, so that only actual tables are displayed in the results, as opposed to other elements of the schema which are stored in the form of tables.
SELECT * FROM pg_tables
Similar to the Oracle syntax, a list of table names in PostGRES can be retrieved using the SELECT statement as well.