- SQL Basics
- SQL Introduction
- SQL Syntax
- SQL Sample Database
- SQL SELECT
- SQL WHERE
- SQL ORDER BY
- SQL DISTINCT
- SQL LIMIT
- SQL FETCH
- SQL AND
- SQL OR
- SQL BETWEEN
- SQL IN
- SQL LIKE
- SQL IS NULL
- SQL Comparison Operators
- SQL Logical Operators
- SQL Alias
- SQL CASE
- Joins and Subqueries
- SQL INNER JOIN
- SQL LEFT JOIN
- SQL RIGHT JOIN
- SQL FULL OUTER JOIN
- SQL SELF JOIN
- SQL CROSS JOIN
- SQL Subquery
- SQL Correlated Subquery
- SQL UNION
- SQL INTERSECT
- SQL EXCEPT
- Aggregate Functions
- SQL AVG
- SQL COUNT
- SQL MAX
- SQL MIN
- SQL SUM
- SQL GROUP BY
- SQL HAVING
- SQL ROLLUP
- SQL CUBE
- SQL GROUPING SETS
- Database Management
- SQL CREATE DATABASE
- SQL ALTER DATABASE
- SQL DROP DATABASE
- SQL BACKUP DATABASE
- SQL SHOW DATABASES
- SQL SELECT DATABASE
- Table Management
- SQL CREATE TABLE
- SQL ALTER TABLE
- SQL ADD COLUMN
- SQL DROP COLUMN
- SQL DROP TABLE
- SQL TRUNCATE TABLE
- SQL SHOW TABLES
- SQL RENAME TABLE
- SQL Constraints
- SQL Primary Key
- SQL Foreign Key
- SQL UNIQUE Constraint
- SQL CHECK Constraint
- SQL NOT NULL Constraint
SQL SHOW TABLES
When you first connect to a database server, especially one you didn't design yourself, your first task is usually discovery. The SHOW TABLES statement is a diagnostic command used to list all the tables within the currently selected database. It is an essential tool for developers to explore the database schema and verify that specific tables exist before writing complex queries against them.
SHOW TABLES frequently when working in a command-line interface (CLI) to orient yourself, or when debugging a script that claims a table "does not exist."
Key Features of SQL SHOW TABLES
- Schema Discovery: It provides an immediate snapshot of the database structure by listing every base table and view.
- Validation: It helps you confirm that migrations or imports have been completed successfully by checking if the new tables appear in the list.
- Specific Context: It only lists tables for the database you are currently "using," keeping the output clean and relevant.
- Engine Support: While it is not part of the standard ANSI SQL specification, it is the primary method for table discovery in MySQL and MariaDB.
USE database_name; command first, SHOW TABLES will return an error because the system doesn't know which database to look inside.
SQL SHOW TABLES Syntax
The syntax is straightforward. You do not need to provide parameters unless you want to filter the results.
SHOW TABLES;
If you want to find a specific set of tables (for example, all tables related to "users"), you can use the LIKE operator:
SHOW TABLES LIKE 'user%';
Example: Listing All Tables
In a real-world scenario, you would first tell the database engine which database you are interested in, and then request the list of tables.
USE company_erp;
SHOW TABLES;
- The
USEstatement sets the context to thecompany_erpdatabase. - The
SHOW TABLESstatement then scans that specific database's metadata and returns the names of all tables.
SHOW TABLES LIKE '%keyword%'; to narrow down your search and save time scrolling through results.
Example Output
The result of this command is a single-column result set where the header is typically formatted as Tables_in_[database_name].
| Tables_in_my_database |
|---|
| Employees |
| Departments |
| Projects |
SHOW TABLES also displays Views (virtual tables) in MySQL. If you need to distinguish between a physical table and a view, you might need to use SHOW FULL TABLES;, which adds a second column identifying the "Table_type."
Alternative Commands for Other Databases
Because SHOW TABLES is specific to MySQL and MariaDB, other database management systems (DBMS) use different methods often querying the information_schema, which is a standardized way to look up database metadata.
Summary
SHOW TABLESis the fastest way to list all tables in your current MySQL or MariaDB database.- It is a read-only operation and does not affect your data.
- If you are using PostgreSQL, SQL Server, or Oracle, you must use the specific system queries mentioned in the table above.
- Combine it with
LIKEto filter through large databases efficiently.