How to Show Tables in SQL

Published : 30 December 2024
Updated : 01 January 2025

185 Views

When working with a relational database. There are many situations where you might need to retrieve a list of all tables from your database, such as for testing, identifying existing tables before making changes or other purposes. SQL provides various commands to display table information and the exact command depends on the database system you are using (e.g., MySQL, PostgreSQL, SQL Server, or Oracle).

Listing Tables in MySQL

In MySQL, the SHOW TABLES command is used to display all the tables in the currently selected database.

USE testDB;
SHOW TABLES;

Tables_in_testDB
-------------------
departments
employees

Filter Tables

There can be a case, where your database may have lot of tables and you might need to filter tables by name. You can do this with the help of LIKE query.

SHOW TABLES LIKE 'pattern';

Example

SHOW TABLES LIKE 'Emp%';

Tables_in_testDB (Emp%)
-------------------------
employees

Listing Tables in SQL Server

SQL Server does not have a direct SHOW TABLES command. Instead of this, you can retrieve table information using views such as sys.tables, INFORMATION_SCHEMA.TABLES or sysobjects.

1. Using SYS.TABLES

The SYS.TABLES view provides information about all user-defined tables in the database.

SELECT * FROM SYS.TABLES;

Example

SELECT name, object_id, schema_id
FROM SYS.TABLES;

+----------+-----------+-----------+
| name     | object_id | schema_id |
+----------+-----------+-----------+
| Departments | 4195065   | 1         |
| Employees   | 68195293  | 1         |
+----------+-----------+-----------+

2. Using INFORMATION_SCHEMA.TABLES

The INFORMATION_SCHEMA.TABLES view is ANSI SQL-compliant and provides metadata about tables.

SELECT * FROM INFORMATION_SCHEMA.TABLES;

Example

SELECT table_name, table_type 
FROM INFORMATION_SCHEMA.TABLES;

+-----------+------------+
| table_name| table_type |
+-----------+------------+
| Departments  | BASE TABLE |
| Employees    | BASE TABLE |
+-----------+------------+

3. Using SYSOBJECTS

The SYSOBJECTS view contains all database objects. To list only user-defined tables.

SELECT * FROM sysobjects;

Example

SELECT name, id, xtype 
FROM sysobjects 
WHERE xtype = 'U';

+----------+-----------+------+
| name     | id        | xtype|
+----------+-----------+------+
| Departments | 4195065   | U |
| Employees   | 68195293  | U |
+----------+-----------+------+

Listing Tables in Oracle

Oracle Database also does not provide a direct SHOW TABLES command like MySQL. Instead of this, Oracle uses system views such as ALL_TABLES, USER_TABLES and DBA_TABLES to retrieve information about tables.

1. Using ALL_TABLES

The ALL_TABLES view displays all tables accessible to the current user, including tables owned by other users.

SELECT * FROM ALL_TABLES;

3. Using USER_TABLES

The USER_TABLES view displays only tables owned by the current user.

SELECT * FROM USER_TABLES;

3. Using DBA_TABLES

The DBA_TABLES view displays all tables in the database. This view requires DBA privileges.

SELECT * FROM DBA_TABLES;

Notes

Use SHOW TABLES command in MySQL for quick table listing.
SQL Server provides views such as SYS.TABLES, INFORMATION_SCHEMA.TABLES and sysobjects for detailed table information.
Oracle offers specific views (ALL_TABLES, USER_TABLES, DBA_TABLES) to list tables depending on the scope and privileges.Filtering results using patterns with the LIKE operator and conditions with the WHERE clause makes listing tables more efficient by narrowing down the search to relevant results.