DuckDB is a fast, efficient, and embeddable SQL database designed for analytical workloads. As with any other RDBMS, a common task is listing all the tables in your DuckDB instance.

This article covers five options for displaying a list of tables in DuckDB.

The .tables Command

If you’re using the DuckDB command line interface (CLI), the quickest way to get a list of tables is with the .tables command.

Example:

.tables

Sample output:

Customers   Orders      Products    t1        
OrderItems  Person      Vendors   

The PRAGMA show_tables Command

Another option for those using the DuckDB CLI is the PRAGMA show_tables command:

Example:

PRAGMA show_tables;

Sample output:

+------------+
|    name    |
+------------+
| Customers  |
| OrderItems |
| Orders     |
| Person     |
| Products   |
| Vendors    |
| t1         |
+------------+

Using the information_schema.tables View

DuckDB provides an SQL-compliant system catalog called information_schema. This include a tables view that you can use to view details about tables:

SELECT table_name 
FROM information_schema.tables
WHERE table_schema = 'main';

Sample output:

+------------+
| table_name |
+------------+
| Customers  |
| OrderItems |
| Orders     |
| Person     |
| Products   |
| t1         |
| Vendors    |
+------------+

Here I listed just one column, but the view contains many other columns that you can include, such as table_catalog, table_schema, table_type, etc. As with any query, you can also use the asterisk wildcard (*) to return all tables.

Leveraging the SQL Editor or Client Interface

Most SQL editors or interfaces compatible with DuckDB (e.g., DBeaver, DataGrip) provide graphical tools to explore schemas and tables.

To do this, navigate to the schema explorer or equivalent to see all tables. Usually you can expand each table by clicking it in order to show its columns, data types, etc.

This option is ideal for those who prefer graphical interfaces over command-line tools.

Writing a Custom Query with sqlite_schema

For users familiar with SQLite, DuckDB’s architecture allows querying metadata via sqlite_schema:

SELECT name  
FROM sqlite_schema  
WHERE type = 'table';

Sample output:

+------------+
|    name    |
+------------+
| Customers  |
| OrderItems |
| Orders     |
| Person     |
| Products   |
| t1         |
| Vendors    |
+------------+

The sqlite_schema table is also available under the following names (for historical compatibility):

• sqlite_master
• sqlite_temp_schema
• sqlite_temp_master

Why List Tables in DuckDB?

Listing tables can be useful for things like:

• Database Exploration: Quickly understanding the available datasets.
• Debugging and Testing: Ensuring expected tables are created or loaded correctly.
• Documentation: Keeping track of database schemas and structures for future reference.

Best Practices for Managing DuckDB Tables

Knowing how to list all tables in DuckDB can help with table management and maintaining best practices. For example, some best practices around table management could include:

• Regular Documentation: Maintain a record of table structures and purposes.
• Automate Schema Checks: Use scripts to periodically verify table presence.
• Adopt Naming Conventions: Consistent table names make querying and debugging easier.