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.
Frequently Asked Questions (FAQs)
1. What does “show tables” mean in SQL?
Showing tables in SQL means retrieving a list of all tables available in a specific database. This helps developers and database administrators understand the database structure, verify existing tables and avoid mistakes such as creating duplicate tables or querying non-existent ones. It is usually one of the first steps when exploring an unfamiliar database.
2. Why is it important to list tables before running SQL queries?
Listing tables helps ensure you are working with the correct database objects. Before running SELECT, INSERT, UPDATE or DELETE queries, checking the available tables prevents errors, avoids accidental changes to the wrong table and improves confidence when working in production or shared environments.
3. Which SQL command is used to show tables in MySQL?
In MySQL, the SHOW TABLES command is used to display all tables in the currently selected database. It is simple, fast and commonly used during development and debugging. However, it works only after a database is selected using the USE command.
4. Why does SHOW TABLES not work in SQL Server or Oracle?
SHOW TABLES is a MySQL-specific command. SQL Server and Oracle follow different metadata standards and expose table information through system views instead. This design allows more detailed and controlled access to database metadata, especially in enterprise environments.
5. How can I list tables in SQL Server correctly?
In SQL Server, tables can be listed using system views such as sys.tables or INFORMATION_SCHEMA.TABLES. These views provide structured metadata about tables, including schema, type and ownership. They are reliable and widely used in professional SQL Server environments.
6. What is the difference between SYS.TABLES and INFORMATION_SCHEMA.TABLES?
SYS.TABLES is SQL Server–specific and provides detailed system-level metadata. INFORMATION_SCHEMA.TABLES is ANSI-standard and works across multiple databases. While SYS views offer more details, INFORMATION_SCHEMA is preferred for portability and cleaner metadata access.
7. Why is SYSOBJECTS considered outdated in SQL Server?
sysobjects is an older system view retained mainly for backward compatibility. Microsoft recommends using sys.tables and other modern catalog views instead. New development should avoid sysobjects to ensure long-term compatibility and cleaner metadata queries.
8. How do I filter tables by name in MySQL?
In MySQL, you can filter table names using the LIKE operator with SHOW TABLES. This is useful when a database contains many tables and you want to locate specific ones quickly based on naming patterns.
9. How can I list only user-defined tables and not system tables?
Most databases separate system tables from user tables. In SQL Server, querying sys.tables automatically returns only user-defined tables. In Oracle, USER_TABLES lists tables owned by the current user, excluding system objects.
10. What is the difference between USER_TABLES, ALL_TABLES and DBA_TABLES in Oracle?
These views differ by scope. USER_TABLES shows tables owned by the current user. ALL_TABLES shows tables the user can access, including others’ tables. DBA_TABLES lists all tables in the database but requires DBA privileges.
11. Do I need special permissions to view tables?
Yes. You can only view tables that you own or have permission to access. In restricted environments, users may see only a limited subset of tables. Higher-level views like DBA_TABLES require administrative privileges.
12. Can listing tables affect database performance?
Listing tables is a metadata operation and generally very lightweight. It does not scan actual table data, so performance impact is minimal. However, querying metadata repeatedly in large enterprise databases should still be done thoughtfully.
13. Is listing tables a common SQL interview question?
Yes. It is a basic but important SQL interview topic. Interviewers use it to test familiarity with database systems and understanding of differences between MySQL, SQL Server and Oracle commands.
14. How do I ensure I’m viewing tables from the correct database?
Always confirm the active database before listing tables. In MySQL, use USE database_name. In SQL Server, check the database context in your session. This prevents confusion when multiple databases exist on the same server.
15. When should I use INFORMATION_SCHEMA instead of database-specific views?
Use INFORMATION_SCHEMA when writing portable SQL scripts that may run on different database systems. For database-specific administration tasks, native system views like sys.tables or USER_TABLES are often more detailed and efficient.