Sql List All Tables In A Database

Introduction

If you are working with a relational database and need to SQL list all tables in a database, you have come to the right place. Day to day, whether you are a beginner learning SQL or an experienced developer managing complex data environments, knowing how to retrieve a comprehensive list of tables is an essential skill. Still, you will also discover why this capability matters, how it can be used in real‑world scenarios, and where you might encounter common pitfalls. This article walks you through the most common methods for enumerating tables across major database platforms—MySQL, PostgreSQL, Microsoft SQL Server, Oracle, and SQLite. By the end of this guide, you will be able to run a single command (or a few lines of code) and instantly see every table that exists within your database, complete with useful metadata such as row counts, schema names, and object types.

Real talk — this step gets skipped all the time Worth keeping that in mind..

Why List All Tables?

Before diving into the commands, it’s helpful to understand the practical reasons for listing tables.

• Database inventory – When you inherit a new database, a quick table list gives you an immediate overview of the data structures.
• Schema documentation – Automated tools often generate ER diagrams or data dictionaries by reading table metadata.
• Security audits – Identifying tables that contain sensitive columns helps you enforce proper access controls.
• Performance tuning – Knowing which tables are large or heavily indexed guides index creation and query optimization.
• Backup and migration – A complete table list ensures you do not miss any objects when scripting a restore or a move to a new platform.

Having a reliable method to SQL list all tables in a database therefore streamlines many administrative tasks and supports better data governance.

General Approach Across Platforms

All relational databases store table definitions in system catalogs or information schemas. The exact views or tables you query differ by vendor, but the underlying principle is the same: join the catalog views that describe user objects and filter out system objects if you only want user‑created tables. Below you will find the specific queries for each major DBMS.

MySQL / MariaDB

MySQL exposes table information through the information_schema database. The most straightforward way to SQL list all tables in a database is:

SELECT TABLE_NAME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database_name'
  AND TABLE_TYPE = 'BASE TABLE';

• TABLE_SCHEMA filters by the target database.
• Adding AND TABLE_TYPE = 'VIEW' includes views if you need them.

If you also want the engine and row count, extend the query:

SELECT TABLE_NAME, ENGINE, TABLE_ROWS
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_TYPE = 'BASE TABLE';

The DATABASE() function dynamically returns the current database, making the query reusable It's one of those things that adds up. But it adds up..

PostgreSQL

PostgreSQL stores metadata in the information_schema as well, but you can also query the pg_catalog schema for more detailed information. To SQL list all tables in a database, use:

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

This excludes system schemas, leaving only user‑defined tables. For a more comprehensive view that includes views and materialized views:

SELECT schemaname, tablename, tabletype
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
UNION ALL
SELECT schemaname, mattablename, 'MATERIALIZED VIEW'
FROM pg_matviews
WHERE schemaname NOT IN ('pg_catalog', 'information_schema');

If you prefer the standard information_schema:

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

Microsoft SQL Server

SQL Server provides several system views for catalog information. The most common method to SQL list all tables in a database is:

SELECT t.NAME AS table_name,
       s.NAME AS schema_name,
       p.rows AS row_count
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
LEFT OUTER JOIN sys.partitions p ON t.object_id = p.object_id
WHERE s.NAME NOT IN ('sys', 'information_schema')
GROUP BY t.NAME, s.NAME, p.rows
ORDER BY t.NAME;

• sys.tables contains user tables.
• Joining sys.schemas gives you schema names.
• sys.partitions supplies approximate row counts (useful for large tables).

To also include views, add OR t.is_view = 1 to the WHERE clause or query sys.views separately.

Oracle

Oracle’s catalog is accessed via the DBA_ or ALL_ views, depending on your privileges. A simple way to SQL list all tables in a database is:

SELECT owner, table_name, num_rows
FROM all_tables
WHERE owner NOT IN ('SYS', 'OUTLN', 'XDB')
ORDER BY owner, table_name;

• ALL_TABLES shows tables accessible to the current user.
• NUM_ROWS provides an approximate row count (may be stale).

If you have DBA privileges, DBA_TABLES returns rows for every table in the database, including those in system schemas Small thing, real impact..

SQLite

SQLite does not have a built‑in information schema, but you can query the sqlite_master table:

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

• type = 'table' filters out indexes and triggers.
• The LIKE exclusion removes internal SQLite system tables.

If you need schema information (columns, constraints), you can join pragma_table_info(name) for each table, but for a simple list the query above suffices Easy to understand, harder to ignore..

Advanced Techniques

Filtering by Size or Row Count

Sometimes you want a quick snapshot of the largest tables. In MySQL:

SELECT TABLE_NAME, TABLE_ROWS
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_ROWS DESC
LIMIT 10;

In PostgreSQL:

SELECT schemaname, tablename, n_live_tup AS live_rows
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC
LIMIT 10;

Including Dependencies

To understand how tables relate to each other, you can query foreign key information. In SQL Server:

SELECT f.name AS foreign_key_name,
       t.name AS table_name,
       c.name AS column_name,
       referenced_object_schema = sch.name,
       referenced_object_name = rp.name,
       referenced_column_name = cp.name
FROM sys.foreign_keys f
INNER JOIN sys.tables t ON f.parent_object_id = t.object_id
INNER JOIN sys.columns c ON f.parent_object_id = c.object_id AND f.parent_column_id = c.column_id
INNER JOIN sys.schemas sch ON f.schema_id = sch.schema_id
INNER JOIN sys.objects rp ON f.referenced_object_id = rp.object_id
INNER JOIN sys.columns cp ON f.referenced_object_id = cp.object_id AND f.referenced_column_id = cp.column_id;

Scripting the Output

If you need to generate a script that can be run on another database, you can wrap the query in a stored procedure or a script block. Take this: in MySQL you could create a procedure:

DELIMITER //
CREATE PROCEDURE ListTables()
BEGIN
  SELECT TABLE_NAME
  FROM information_schema.TABLES
  WHERE TABLE_SCHEMA = DATABASE()

### MySQL – Encapsulating the List in a Stored Procedure  

Below is a complete version of the procedure that returns the names of all user‑owned tables in the current database:

```sql
DELIMITER //

CREATE PROCEDURE ListTables()
BEGIN
  SELECT TABLE_NAME
  FROM information_schema.TABLES
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_TYPE   = 'BASE TABLE';
END //

DELIMITER ;

How to use it

CALL ListTables();

The procedure filters out system‑generated tables (TABLE_TYPE = 'BASE TABLE') and limits results to the schema that matches the current database connection, giving you a clean, repeatable way to obtain a table list from within MySQL That's the whole idea..

PostgreSQL

PostgreSQL also ships with a reliable information schema, but many developers prefer the pg_catalog views for raw performance. Two common approaches are:

1. Using information_schema.tables

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

2. Directly querying pg_class and pg_namespace

SELECT n.nspname AS schema_name,
       c.relname   AS table_name
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'               -- 'r' = regular table
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY n.nspname, c.relname;

Both queries ignore system schemas, giving you a concise list of user tables.

SQL Server

SQL Server makes table discovery straightforward via the sys.tables catalog view:

SELECT SCHEMA_NAME(schema_id) AS schema_name,
       name                 AS table_name
FROM   sys.tables
WHERE  is_ms_shipped = 0        -- exclude system objects
ORDER BY schema_name, name;

If you also need the row count, join to sys.partitions:

SELECT SCHEMA_NAME(t.schema_id) AS schema_name,
       t.name                   AS table_name,
       SUM(p.rows)              AS row_count
FROM   sys.tables t
JOIN   sys.partitions p ON p.object_id = t.object_id
WHERE  t.is_ms_shipped = 0
GROUP BY SCHEMA_NAME(t.schema_id), t.name
ORDER BY row_count DESC;

Generic Scripting Across Multiple Platforms

When you need a single script that works on several RDBMS platforms, you can wrap each dialect in conditional blocks that check the database product name:

/*  Example: Run the appropriate query based on DB vendor  */
DO
$
BEGIN
  IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'mysql') THEN
     -- MySQL / MariaDB
     SELECT TABLE_NAME AS table_name
     FROM information_schema.TABLES
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_TYPE = 'BASE TABLE';
  ELSIF EXISTS (SELECT 1 FROM pg_catalog.pg_class WHERE relkind = 'r') THEN
     -- PostgreSQL
     SELECT c.relname AS table_name
     FROM pg_catalog.pg_class c
     JOIN pg_namespace n ON n.oid = c.relnamespace
     WHERE c.relkind = 'r' AND n.nspname NOT IN ('pg_catalog','information_schema');
  ELSIF EXISTS (SELECT 1 FROM sys.tables) THEN
     -- SQL Server
     SELECT name AS table_name
     FROM   sys.tables
     WHERE  is_ms_shipped = 0;
  END IF;
END;
$

The exact syntax will vary (e.g., MySQL uses SELECT‑only blocks, while PostgreSQL requires PL/pgSQL), but the pattern demonstrates how to abstract table‑listing logic.

Putting It All Together

Regardless of the platform you work with, obtaining a reliable list of tables is the first step

Regardless of the platform you work with, obtaining a reliable list of tables is the first step toward effective schema management, impact analysis, and automation. Whether you are building a dynamic data dictionary, scripting deployment pipelines, or simply exploring an unfamiliar database, the queries above give you a solid, standards-compliant foundation Simple, but easy to overlook. Still holds up..

A few best practices will keep your metadata queries solid as environments evolve:

• Filter system objects explicitly. Relying solely on schema name exclusions (e.g., NOT IN ('pg_catalog', 'information_schema')) is safer than assuming user tables live only in public or dbo. Custom system schemas or third-party extensions often create tables in their own namespaces.
• Prefer information_schema for portability. When writing code intended to run on multiple engines, the ANSI-standard views (TABLES, COLUMNS, VIEWS) minimize dialect-specific rewrites. Reserve native catalogs (pg_class, sys.tables) for performance-critical scripts or when you need engine-specific metadata such as partition counts, fill factors, or toast tables.
• Parameterize the schema context. Hard-coding DATABASE() or CURRENT_SCHEMA works for ad-hoc exploration, but reusable scripts should accept a schema parameter so they can be executed against any namespace without code changes.
• Mind permissions. Metadata visibility is governed by the same GRANT/REVOKE system as data. A low-privilege role may see only a subset of tables; always test your discovery queries under the same security context your automation will use.

By internalizing these patterns—standard views for portability, native catalogs for depth, and disciplined filtering for accuracy—you turn a routine “show me the tables” request into a reliable, repeatable component of your data engineering toolkit It's one of those things that adds up..