List Tables in SQL: MySQL, MariaDB, PostgreSQL & More

Table-listing syntax depends on the database client and the database you are connected to. MySQL and MariaDB use SHOW TABLES; PostgreSQL’s \dt and SQLite’s .tables are command-line shortcuts; SQL Server and Oracle expose catalog views you can query. Choose the command that matches your database and scope.

If you need to identify or choose a database before listing tables, see List Databases in SQL, which also explains PostgreSQL schemas, Oracle users, and SQLite’s connection-scoped databases.

Quick reference

MySQL and MariaDB

Run SHOW TABLES after selecting a database. To name the database directly, use FROM:

SHOW TABLES FROM sakila;

The command also lists views, and the result only includes objects visible to your account. For MariaDB, SHOW TABLES also reports sequences. See the MySQL SHOW TABLES reference and the MariaDB SHOW TABLES reference. SQLiz has detailed guides for MySQL and choosing a MySQL database.

PostgreSQL

In the psql command-line client, use its \dt meta-command to list user-created tables visible on the current search_path. Use \dt *.* to list tables across schemas; because a pattern is specified, this also includes system objects. The SQL query below filters out pg_catalog and information_schema when you want only application schemas. These backslash commands are psql shortcuts, not SQL statements.

For a SQL query across schemas in the current database, use pg_catalog.pg_tables:

SELECT schemaname, tablename
FROM pg_catalog.pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY schemaname, tablename;

For client commands and visibility rules, see the official psql manual. For more examples, see SQLiz’s guide to listing PostgreSQL tables.

SQL Server

Join sys.tables to sys.schemas to list visible user tables with schema names:

SELECT s.name AS schema_name, t.name AS table_name
FROM sys.tables AS t
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
ORDER BY s.name, t.name;

The query checks the current database. SQL Server limits catalog visibility to objects that the account owns or has permission to access. See Microsoft’s sys.tables and sys.schemas references and SQLiz’s SQL Server table-listing tutorial.

SQLite

In the sqlite3 command-line shell, .tables lists tables and views in the open and attached databases. It is a dot-command, not SQL. For a query against the current database’s schema table, select only table objects and skip SQLite’s internal objects:

SELECT name
FROM sqlite_schema
WHERE type = 'table'
  AND name NOT GLOB 'sqlite_*'
ORDER BY name;

sqlite_schema stores tables, indexes, views, and triggers; type = 'table' filters to tables. See SQLite’s command-line shell and sqlite_schema reference.

Oracle

Query USER_TABLES to list relational tables owned by the current user:

SELECT table_name
FROM user_tables
ORDER BY table_name;

Use ALL_TABLES when you need relational tables accessible to the current user across schemas; it includes an OWNER column. DBA_TABLES describes all database tables and requires appropriate privileges. See Oracle’s USER_TABLES and ALL_TABLES references.

If an expected table is missing

Check that you are connected to the intended database, then check the schema or search path for your platform. Catalog and metadata views only show objects visible under your account’s permissions. Also check whether the object is a table or a view; some commands include both unless you filter by type.

Browse the cross-database SQL comparison guides for other syntax differences across database engines.

To inspect the columns after locating a table, see list table columns across databases.