How to List Tables in SQL: A Simple Example

In SQL, there are often times when you need to get a list of all tables in your database—especially when working with large databases, managing schema, or understanding the structure of unfamiliar data. In this guide, we’ll cover the most common ways to retrieve a list of tables in SQL with straightforward examples.

1. Using INFORMATION_SCHEMA.TABLES

Most SQL databases, like MySQL, SQL Server, and PostgreSQL, support querying system views to access information about tables. The INFORMATION_SCHEMA.TABLES view is a popular way to retrieve table data.

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = 'your_database_name';

Explanation:

• TABLE_NAME: The column that stores the names of the tables.
• TABLE_TYPE: Setting this to 'BASE TABLE' ensures you only get actual tables, not views.
• TABLE_SCHEMA: Filter by your database name to list tables specific to it.

Example Output:

+----------------+
| TABLE_NAME     |
+----------------+
| employees      |
| departments    |
| salaries       |
+----------------+

2. Using SHOW TABLES in MySQL

If you’re using MySQL or MariaDB, you can get a list of tables using the SHOW TABLES command. This is a simple, direct way to retrieve table names without needing to query INFORMATION_SCHEMA.

SHOW TABLES;

Explanation:

• Output: This command returns a list of all table names in the currently selected database.

Example Output:

+----------------+
| Tables_in_your_database |
+----------------+
| employees      |
| departments    |
| salaries       |
+----------------+

Using pg_catalog.pg_tables in PostgreSQL

In PostgreSQL, you can use the pg_catalog.pg_tables view to list tables in the database.

SELECT tablename
FROM pg_catalog.pg_tables
WHERE schemaname = 'public';

Explanation:

• tablename: The column that stores the table names.
• schemaname: Setting this to 'public' will filter tables to those in the public schema, which is the default schema for most user tables in PostgreSQL.

Example Output:

+-------------+
| tablename   |
+-------------+
| employees   |
| departments |
| salaries    |
+-------------+

Using sys.tables in SQL Server

In Microsoft SQL Server, you can use the sys.tables catalog view to retrieve a list of tables in your database.

SELECT name
FROM sys.tables;

Explanation:

• name: The column that holds the names of the tables in the database.
• Result: This query returns a simple list of all tables within the current database.

Example Output:

+-------------+
| name        |
+-------------+
| employees   |
| departments |
| salaries    |
+-------------+

Conclusion

To quickly get a list of tables in SQL, you have several options:

• INFORMATION_SCHEMA.TABLES: Works across many SQL databases.
• SHOW TABLES: A straightforward option for MySQL and MariaDB.
• pg_catalog.pg_tables: Ideal for PostgreSQL users.
• sys.tables: A quick method for SQL Server.

Choose the method that best suits your database environment, and you’ll be able to get a clear view of the table structure in your SQL database. This will make managing and querying your data even easier!

Happy Coding…

Subscribe to Youtube Channel