MySQL SHOW Commands: List Databases, Tables, Users and More

When you log into an unfamiliar MySQL or MariaDB server, the first questions are always the same: which databases are here, what tables do they contain, how big are they, and who has access? These commands answer all of it.

Connect first:

sudo mysql                     # Ubuntu/Debian with socket auth as root
mysql -u admin -p              # with a password

Databases

SHOW DATABASES;
SHOW DATABASES LIKE 'shop%';
SELECT DATABASE();             -- which database is currently selected
USE shop;

From the shell, without entering the client:

sudo mysql -e "SHOW DATABASES;"

information_schema, performance_schema, mysql and sys are system databases — don’t modify them directly.

Tables

SHOW TABLES;                   -- in the current database
SHOW TABLES FROM shop;
SHOW TABLES LIKE 'wp_%';
SHOW FULL TABLES WHERE Table_type = 'VIEW';   -- views only

Table structure

DESCRIBE orders;               -- or: SHOW COLUMNS FROM orders;
SHOW CREATE TABLE orders\G     -- full CREATE statement, incl. indexes, engine, charset
SHOW INDEX FROM orders;

The \G terminator prints results vertically — much easier to read for wide output.

Sizes

Size of each database:

SELECT table_schema AS db,
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS size_mb
FROM information_schema.tables
GROUP BY table_schema
ORDER BY size_mb DESC;

Largest tables in a database:

SELECT table_name,
       ROUND(data_length / 1024 / 1024, 1) AS data_mb,
       ROUND(index_length / 1024 / 1024, 1) AS index_mb,
       table_rows
FROM information_schema.tables
WHERE table_schema = 'shop'
ORDER BY data_length + index_length DESC
LIMIT 10;

table_rows is an estimate for InnoDB. For an exact count use SELECT COUNT(*) FROM orders; (which can be slow on huge tables).

Users and privileges

SELECT user, host, plugin, account_locked FROM mysql.user ORDER BY user;
SHOW GRANTS FOR 'app'@'localhost';
SHOW GRANTS;                   -- current user
SELECT CURRENT_USER();

MariaDB 10.4+ stores accounts in mysql.global_priv, but mysql.user remains available as a view. More on accounts in MySQL create user and grant.

What’s running right now

SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;         -- don't truncate query text

Find long-running queries:

SELECT id, user, db, time, state, LEFT(info, 100) AS query
FROM information_schema.processlist
WHERE command <> 'Sleep' AND time > 5
ORDER BY time DESC;

Stop one:

KILL QUERY 1234;    -- cancel the query, keep the connection
KILL 1234;          -- close the connection

Server status and variables

SELECT VERSION();
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE '%timeout%';
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Questions';
SHOW ENGINE INNODB STATUS\G    -- locks, deadlocks, buffer pool details

SHOW VARIABLES is configuration; SHOW STATUS is counters since the server started. See MySQL performance tuning for what to look at.

Character sets and collations

SELECT schema_name, default_character_set_name, default_collation_name
FROM information_schema.schemata;

SELECT table_name, table_collation
FROM information_schema.tables WHERE table_schema = 'shop';

Mixed collations cause “Illegal mix of collations” errors in joins. Prefer utf8mb4 everywhere — see MySQL utf8mb4.

Stored routines, triggers and events

SHOW PROCEDURE STATUS WHERE Db = 'shop';
SHOW FUNCTION STATUS WHERE Db = 'shop';
SHOW TRIGGERS FROM shop;
SHOW EVENTS FROM shop;

Handy shell one-liners

# Tables and row estimates for one database, tab-separated
sudo mysql -N -e "SELECT table_name, table_rows FROM information_schema.tables WHERE table_schema='shop'"

# Sizes of all databases
sudo mysql -e "SELECT table_schema, ROUND(SUM(data_length+index_length)/1048576) MB FROM information_schema.tables GROUP BY table_schema"

# Check which databases a user can see
mysql -u app -p -e "SHOW DATABASES"

-N drops column headers; -B gives tab-separated output for scripts.

Quick reference

Question	Command
Databases?	`SHOW DATABASES;`
Tables?	`SHOW TABLES;`
Columns?	`DESCRIBE t;`
Indexes?	`SHOW INDEX FROM t;`
Create statement?	`SHOW CREATE TABLE t\G`
Users?	`SELECT user,host FROM mysql.user;`
Privileges?	`SHOW GRANTS FOR 'u'@'h';`
Running queries?	`SHOW FULL PROCESSLIST;`
Version?	`SELECT VERSION();`

MySQL
MariaDB
Databases