Sql List Tables In A Database

4 min read

Listing tables in a database is one of the first practical skills every SQL user needs, whether you are exploring an unfamiliar schema, auditing a production system, or simply trying to remember the name of that one table you created last month. In practice, every major database engine offers a way to list tables in a database, but the syntax differs more than most beginners expect. This guide walks through the standard approach and the engine-specific shortcuts for MySQL, PostgreSQL, SQL Server, Oracle, and SQLite, along with filtering tricks, troubleshooting tips, and best practices that make the process faster and safer Surprisingly effective..

Why Listing Tables Matters

Before you can query data, you need to know what exists. A table list gives you a map of the database: which entities are modeled, how naming conventions are applied, and whether the schema you expected is actually the one you connected to. Common situations where you need this include:

  • Onboarding to a new project where documentation is outdated or missing.
  • Debugging an application error caused by a missing or renamed table.
  • Auditing a database for orphaned, temporary, or legacy tables before cleanup.
  • Writing migrations and needing to confirm current state.
  • Granting permissions by identifying exactly which objects a user should access.

Because table listings are metadata queries, they are usually fast and lightweight. Still, on very large instances with thousands of objects, it pays to filter rather than dump everything Small thing, real impact. But it adds up..

The Universal Approach: INFORMATION_SCHEMA

Most relational databases implement INFORMATION_SCHEMA, a set of read-only views defined by the SQL standard. The TABLES view is the most portable way to list tables:

SELECT table_schema, table_name, table_type
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
ORDER BY table_schema, table_name;

The table_type filter is the kind of thing that makes a real difference. Think about it: without it, you will also see views, and in some engines system tables as well. BASE TABLE restricts results to real, physical tables Small thing, real impact. Took long enough..

This query works in MySQL, PostgreSQL, SQL Server, and many others. Oracle is the notable exception — it does not expose INFORMATION_SCHEMA, so you must use its own data dictionary views instead.

MySQL and MariaDB

MySQL offers the friendliest shortcut:

SHOW TABLES;

To inspect a different database without switching context:

SHOW TABLES FROM sales_db;

SHOW FULL TABLES adds a Table_type column so you can distinguish base tables from views. For programmatic use, the information_schema query is preferable because it returns structured rows you can filter and join:

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'sales_db'
  AND table_type = 'BASE TABLE';

A useful bonus in MySQL is the TABLE_ROWS column, which gives an approximate row count. For InnoDB tables this value is an estimate, not an exact figure, so never rely on it for billing or reporting accuracy.

PostgreSQL

Inside the psql command-line client, the meta-commands are the fastest route:

  • \dt lists tables in the current schema.
  • \dt *.* lists tables across all schemas.
  • \dt+ adds size and description columns.
  • \d table_name describes a specific table's columns and indexes.

In plain SQL, you have two good options:

-- Standard-compliant
SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
  AND table_type = 'BASE TABLE';

-- PostgreSQL catalog
SELECT schemaname, tablename
FROM pg_catalog.pg_tables
WHERE schemaname = 'public';

The pg_tables view is slightly faster and includes the table owner, which is handy during permission audits.

SQL Server

SQL Server supports both the standard view and its own catalog:

SELECT schema_name(schema_id) AS schema_name, name
FROM sys.tables
ORDER BY schema_name, name;

The sys.tables catalog view is the recommended approach because it exposes useful metadata such as create_date and modify_date. Day to day, objectsview withtype = 'U'still works but is less descriptive. The oldersys.You can also run sp_tables or sp_help for a quick interactive look Turns out it matters..

To find recently created tables:

SELECT name, create_date
FROM sys.tables
WHERE create_date > DATEADD(day, -7, GETDATE());

Oracle

Oracle uses data dictionary views scoped by privilege level:

  • USER_TABLES — tables owned by the current user.
  • ALL_TABLES — tables the current user can access.
  • DBA_TABLES — all tables in the database (requires elevated privileges).
SELECT table_name
FROM user_tables
ORDER BY table_name;

If you are unsure which schema a table lives in, query ALL_TABLES and filter on owner. Oracle also lets you run SELECT * FROM tab; as a quick legacy shortcut, though it returns less detail.

SQLite

SQLite stores its schema in a single internal table:

SELECT name
FROM sqlite_master
WHERE type = 'table'
  AND name NOT LIKE 'sqlite_%';

The sqlite_% filter removes internal tables such as sqlite_sequence. tablesprints a compact list, and.In the SQLite CLI, .schema table_name shows the CREATE statement.

Filtering and Searching Table Names

Once you know the pattern, filtering is straightforward. Use LIKE with wildcards to search by fragment

Just Went Live

Newly Published

People Also Read

Other Angles on This

Thank you for reading about Sql List Tables In A Database. We hope the information has been useful. Feel free to contact us if you have any questions. See you next time — don't forget to bookmark!
⌂ Back to Home