How to Show Tables in SQL
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.