How do I show all tables in MySQL?

Select a database with USE db_name, then run SHOW TABLES. To skip the USE step, run SHOW TABLES FROM db_name. Add FULL to also see whether each object is a BASE TABLE or a VIEW.

Why does SHOW TABLES say 'No database selected'?

SHOW TABLES lists tables in the current database and no database is selected yet. Either run USE your_database first, or name it inline with SHOW TABLES FROM your_database.

Is SHOW SCHEMAS the same as SHOW DATABASES?

Yes. The MySQL documentation states that SHOW SCHEMAS is a synonym for SHOW DATABASES. In MySQL a schema and a database are the same thing, which is different from PostgreSQL where a schema is a namespace inside a database.

Why does SHOW DATABASES not list all my databases?

You only see databases for which you have some kind of privilege, unless you hold the global SHOW DATABASES privilege. If the server was started with --skip-show-database, users without that privilege cannot run the statement at all.

How do I list users in MySQL?

There is no SHOW USERS statement. Query the system table directly: SELECT User, Host FROM mysql.user. You need privileges on the mysql database to read it, so this usually means connecting as root or another administrative account.

What is the difference between DESCRIBE and SHOW COLUMNS?

None of substance. DESCRIBE is a shorthand for SHOW COLUMNS FROM, and DESC is a shorthand for DESCRIBE. All three return the same column list. SHOW FULL COLUMNS adds the collation, privileges and column comment.

How do I list tables from a script rather than the command line?

Query information_schema.TABLES, filtering on TABLE_SCHEMA. It returns a normal result set you can join and filter, which SHOW TABLES does not, so it is the better choice whenever the output feeds other code.

← MySQL guides

SHOW TABLES, SHOW DATABASES and DESCRIBE in MySQL

By Kevin Piacentini, Software Engineer & Founder of FluentDB·Updated August 6, 2026

MySQL is the database that actually has SHOW TABLES, which is why people coming from MySQL are so surprised when it fails in Postgres or SQLite. Here the commands are short and they work as expected.

The parts worth knowing are the ones the one-line answer leaves out: selecting a database first, why the list can be shorter than you expect, the fact that there is no SHOW USERS, and when to use information_schema instead because you are scripting rather than typing.

The short answer

sql

SHOW DATABASES;            -- every database you can see
USE mydb;                  -- pick one
SHOW TABLES;               -- tables in the current database
SHOW TABLES FROM mydb;     -- tables in a named database, no USE needed
DESCRIBE users;            -- columns of one table
SELECT User, Host FROM mysql.user;   -- users (there is no SHOW USERS)

SHOW TABLES, and the two things that trip people up

The full syntax is more capable than the one-liner suggests:

sql

SHOW [EXTENDED] [FULL] TABLES
    [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr]

First, you have to select a database. SHOW TABLESlists tables in the current one, so on a fresh connection it fails with "No database selected". Either USE mydb first, or name it inline with SHOW TABLES FROM mydb.

Second, FULL tells you what each object is. Without it, views and tables are indistinguishable in the list. With it, a second column reports BASE TABLE, VIEW, or SYSTEM VIEW.

sql

SHOW FULL TABLES;                      -- with a type column
SHOW TABLES LIKE 'user%';              -- pattern match
SHOW FULL TABLES WHERE Table_type = 'VIEW';   -- views only

EXTENDED is a rarely used flag with a genuinely useful purpose: it reveals hidden tables left behind by a failed ALTER TABLE, whose names begin with #sql. If a table is mysteriously taking up space, that is where to look. They can be removed with DROP TABLE.

SHOW DATABASES, and why the list may be short

SHOW SCHEMAS is a documented synonym, so the two are interchangeable. Worth noting because it means something different in Postgres, where a schema is a namespace inside a database rather than another word for one.

The surprising part is privileges. You see only databases for which you have some privilege, unless you hold the global SHOW DATABASES privilege. So a short list usually means a restricted account, not a missing database. If the server runs with --skip-show-database, accounts without that privilege cannot run the statement at all.

sql

SHOW DATABASES;
SHOW DATABASES LIKE 'prod%';

-- the information_schema equivalent
SELECT SCHEMA_NAME FROM information_schema.SCHEMATA ORDER BY SCHEMA_NAME;

Describing a table

Three spellings, one result. DESCRIBE is shorthand for SHOW COLUMNS FROM, and DESC is shorthand for DESCRIBE:

sql

DESCRIBE users;
DESC users;
SHOW COLUMNS FROM users;
SHOW FULL COLUMNS FROM users;   -- adds collation, privileges, comment
SHOW COLUMNS FROM users FROM mydb;   -- without switching database

Reach for SHOW FULL COLUMNS when you care about collation, which is the usual culprit behind comparisons that unexpectedly ignore case, or when the table has column comments worth reading.

Listing users

There is no SHOW USERS. Accounts live in a normal table you can query, provided you have privileges on the mysql database:

sql

SELECT User, Host FROM mysql.user ORDER BY User;

MySQL identifies an account by both user and host, so 'app'@'localhost' and 'app'@'%' are two different accounts with potentially different passwords and grants. That pair is the single most common reason a login works from one machine and fails from another.

sql

SHOW GRANTS FOR 'app'@'localhost';

When to use information_schema instead

SHOW statements are built for humans at a prompt. Their output is not a result set you can join, filter properly or feed into other SQL. When the answer is going into a script, query information_schema instead:

sql

-- every base table in one database, with row estimates and size
SELECT TABLE_NAME,
       TABLE_ROWS,
       ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 1) AS size_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mydb'
  AND TABLE_TYPE = 'BASE TABLE'
ORDER BY DATA_LENGTH + INDEX_LENGTH DESC;

One caveat worth stating: TABLE_ROWS is an estimate for InnoDB, not a count. It is fine for spotting which tables are large, and wrong for anything that needs an exact number, where you still need SELECT COUNT(*).

A translation table

MySQL commands and their PostgreSQL and SQLite equivalents
MySQL	PostgreSQL	SQLite
`SHOW DATABASES;`	`\l`	One file is one database
`USE mydb;`	`\c mydb`	Open another file
`SHOW TABLES;`	`\dt`	`.tables`
`SHOW COLUMNS FROM users;`	`\d users`	`PRAGMA table_info('users');`
`DESCRIBE users;`	`\d users`	`PRAGMA table_info('users');`
`SELECT ... mysql.user;`	`\du`	No users, no server

The equivalents for the other engines are covered in listing tables in PostgreSQL and listing tables in SQLite.