SQL SHOW

What is the SQL SHOW statement?

Returns metadata about databases, tables, indexes, server status, or configuration settings.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL SHOW:

SQL SHOW Full Explanation

SQL SHOW is a metadata-retrieval statement, most widely known in MySQL and MariaDB, that queries the server for information rather than data rows. Depending on the sub-command (e.g., DATABASES, TABLES, INDEX, VARIABLES, STATUS, CREATE TABLE), SHOW can list objects, reveal DDL, expose performance counters, or display configuration parameters. Unlike SELECT queries against INFORMATION_SCHEMA, SHOW is parsed and optimized by the server for speed and sometimes exposes extra details not available elsewhere. SHOW never modifies data and is autocommit-safe.Key behaviors- Executes instantly and does not lock user tables.- Output is returned as a result set that can be piped into other SQL statements (e.g., CREATE TABLE foo AS SELECT * FROM (SHOW TABLES) t) in dialects that allow subquery usage.- Many SHOW variants accept LIKE, WHERE, or FROM clauses to filter results.Caveats- Syntax and supported sub-commands vary across databases. Standard SQL does not define SHOW.- Permissions apply: users need the relevant SHOW privilege or object privilege to view metadata.- In replication setups, SHOW statements run on the connected server only; they are not forwarded.- Some variants (e.g., SHOW CREATE TRIGGER) return a single row; others can return thousands, so use LIMIT or WHERE when possible.

SQL SHOW Syntax

SHOW <sub-command> [FROM db_name]
SHOW <sub-command> LIKE 'pattern'
SHOW <sub-command> WHERE expr

SQL SHOW Parameters

sub-command (keyword) - The type of metadata to retrieve, such as DATABASES, TABLES, COLUMNS, INDEX, TRIGGERS, EVENTS, VARIABLES, STATUS, WARNINGS, PROCESSLIST, ENGINE, PRIVILEGES, CREATE table_or_view, etc.
db_name (identifier) - Optional database name to scope the request.
pattern (string) - Optional LIKE pattern for server-side filtering.
expr (expression) - Optional WHERE clause for more complex filtering.

Example Queries Using SQL SHOW

-- List all databases
SHOW DATABASES;

-- Show tables in the current schema
SHOW TABLES;

-- Show columns of a single table
SHOW COLUMNS FROM customers;

-- Display server variables that start with 'innodb'
SHOW VARIABLES LIKE 'innodb%';

-- Reveal the exact DDL of a table
SHOW CREATE TABLE orders;

Expected Output Using SQL SHOW

Each SHOW variant returns a result set whose columns depend on the sub-command
For example, SHOW TABLES returns one column named 'Tables_in_', while SHOW VARIABLES returns two columns 'Variable_name' and 'Value'
No data is modified

Use Cases with SQL SHOW

Quickly audit available databases and tables during exploration.
Inspect schema details before refactoring a table.
Grab ready-to-run DDL for version control with SHOW CREATE.
Check current configuration (e.g., max_connections) without logging into the OS.
Monitor runtime status such as Threads_running or replication Lag.

Common Mistakes with SQL SHOW

Assuming SHOW is portable to all SQL databases. It is not part of the ANSI standard.
Forgetting to add FROM db_name when the session default database is not set, leading to an error.
Using SHOW in subqueries where the dialect does not allow it (e.g., MySQL requires the INFORMATION_SCHEMA workaround).
Confusing SHOW COLUMNS with DESCRIBE in MariaDB, where DESCRIBE is merely an alias.

First Introduced In

MySQL 3.23 (1998)

Frequently Asked Questions

What kinds of things can I SHOW?

You can list databases, tables, columns, indexes, events, triggers, user privileges, server variables, and full DDL with SHOW CREATE.

Can I run SHOW on a read-only replica?

Yes. SHOW only reads metadata and is safe on primaries or replicas. Some status values may differ because they are node-specific.

Does SHOW require special permissions?

A user needs the SHOW DATABASES privilege to view all schemas. Lacking that, they only see schemas they own. Object-level SHOW variants require SELECT on the object.

How is SHOW different from INFORMATION_SCHEMA?

INFORMATION_SCHEMA exposes metadata via standard SELECT queries, while SHOW is vendor-native, faster, and sometimes reveals extra fields (e.g., row format).