How to Get the names of the table in SQL

In SQL databases, it is often necessary to retrieve the list of table names within a database to understand the structure or to perform the certain operations. Each SQL-based database system offers specific ways to query its metadata to extract the table names.

In this article, we will explore how to get the names of tables in popular relational database systems including the MySQL, SQL Server, PostgreSQL and Oracle. We will cover the built-in SQL queries and functions that are commonly used for this task.

Need to Retrieve Table Names?

• Database Exploration: When working with a new or unfamiliar database we may want to explore all the tables to understand its structure.
• Automation: In automation scripts, dynamically retrieving table names can help with tasks like backups, migrations or reporting.
• Metadata Information: It is useful when generating documentation performing the schema comparisons or reviewing the database design.

Retrieving Table Names in Different Databases

Below are the SQL queries to retrieve table names from the various relational databases.

MySQL

In MySQL, the information about the tables is stored in a special schema called information_schema. We can query this schema to get the list of table names. Following is the syntax of the query to Retrieve Table Names in MySQL

SELECT table_name 
FROM information_schema.tables 
WHERE table_schema = 'database_name';

Where,

• information_schema.tables: Contains metadata about tables.
• table_schema: Refers to the database name.
• table_name: Column that stores the names of the tables.

Example

Following query will return the list of tables in the employees database

SELECT table_name 
FROM information_schema.tables 
WHERE table_schema = 'employees';

SQL Server

In SQL Server, we can retrieve the names of tables using the INFORMATION_SCHEMA.TABLES view similar to MySQL.

SELECT table_name 
FROM information_schema.tables 
WHERE table_type = 'BASE TABLE' AND table_catalog = 'database_name';

Where,

• table_type = 'BASE TABLE': Ensures that only actual tables (not views) are retrieved.
• table_catalog: Refers to the database name.

Example

Following is the query to Retrieve Table Names in SQL Server

SELECT table_name 
FROM information_schema.tables 
WHERE table_type = 'BASE TABLE' AND table_catalog = 'SalesDB';

Alternative Query Using System Tables:

We can also use system views like sys.tables in SQL Server. This query will retrieve the names of the all tables in the current database.

SELECT name FROM sys.tables;

PostgreSQL

In PostgreSQL, we can use the pg_tables system catalog to get the names of tables.

SELECT tablename 
FROM pg_tables 
WHERE schemaname = 'public';

Where,

• pg_tables: A system catalog that stores metadata about tables.
• schemaname = 'public': Filters the tables that belong to the default schema public. We can change this if we are using the different schema.

Example:

Following query will return the names of tables in the public schema.

SELECT tablename 
FROM pg_tables 
WHERE schemaname = 'public';

Another Option Using information_schema:

This query achieves the same result but uses the information_schema view.

SELECT table_name 
FROM information_schema.tables 
WHERE table_schema = 'public' AND table_type = 'BASE TABLE';

Oracle

In Oracle, table metadata is stored in the ALL_TABLES, USER_TABLES and DBA_TABLES views. The view we query depends on the scope of the data we need. Following is the query to Retrieve Table Names in Oracle. This will retrieve tables owned by the current user.

SELECT table_name 
FROM user_tables;

To retrieve tables visible to the current use including the tables from other schemas use the following query

SELECT table_name 
FROM all_tables;

This query will return the table names owned by current user in Oracle.

SELECT table_name 
FROM user_tables;

SQLite

In SQLite, we can use the following SQL command to list the names of tables in current database.

SELECT name 
FROM sqlite_master 
WHERE type = 'table';

Where,

• sqlite_master: Stores the schema of the database.
• type = 'table': Filters out non-table objects like indexes and views.

Following query will list all the tables in the current SQLite database.

SELECT name 
FROM sqlite_master 
WHERE type = 'table';

Conclusion

Retrieving table names in SQL is a simple yet powerful operation that can help in exploring the database structure and performing the various automated or manual tasks. The Different database systems provide similar approaches often leveraging the INFORMATION_SCHEMA or system catalogs such as pg_tables, user_tables and sqlite_master.