The duckdb_tables() function is a system function in DuckDB that provides useful metadata about all tables in your database. The function returns a table containing information about each table, including its schema, name, and various properties. It can be quite a handy tool for database introspection and management.
Syntax
The duckdb_tables() function is a table function and so you need to call it in a FROM clause, like this:
SELECT * FROM duckdb_tables();Setting Up Example Tables
Let’s create some sample tables to demonstrate the duckdb_tables() function:
-- Create a simple customers table
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Create an orders table with foreign key
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER,
order_date DATE NOT NULL,
total_amount DECIMAL(10,2) CHECK (total_amount > 0),
status VARCHAR(20) DEFAULT 'pending',
FOREIGN KEY (customer_id) REFERENCES customers(id)
);
-- Create a temporary table
CREATE TEMPORARY TABLE temp_analytics (
metric_name VARCHAR(50),
metric_value DOUBLE,
recorded_at TIMESTAMP
);
-- Create a table in a custom schema
CREATE SCHEMA inventory;
CREATE TABLE inventory.products (
product_id INTEGER PRIMARY KEY,
product_name VARCHAR(200) NOT NULL,
price DECIMAL(8,2),
stock_quantity INTEGER DEFAULT 0
);
-- Insert some sample data
INSERT INTO customers VALUES
(1, 'Blake Briskley', '[email protected]', '2025-01-15'),
(2, 'Amy Toggle', '[email protected]', '2025-01-16'),
(3, 'Fitch Flex', '[email protected]', '2025-01-17');
INSERT INTO orders VALUES
(101, 1, '2025-01-20', 250.00, 'completed'),
(102, 2, '2025-01-21', 150.75, 'pending'),
(103, 1, '2025-01-22', 89.99, 'shipped');
INSERT INTO temp_analytics VALUES
('daily_sales', 490.74, '2025-01-22'),
('active_users', 3, '2025-01-22');
INSERT INTO inventory.products VALUES
(1, 'Laptop', 999.99, 50),
(2, 'Mouse', 29.99, 200),
(3, 'Keyboard', 79.99, 100);List All Tables
Let’s start by listing all columns for all tables:
SELECT * FROM duckdb_tables()
ORDER BY schema_name, table_name;Output:
+---------------+--------------+-------------+------------+----------------+-----------+---------+------+----------+-----------+-----------------+----------------+--------------+-------------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| database_name | database_oid | schema_name | schema_oid | table_name | table_oid | comment | tags | internal | temporary | has_primary_key | estimated_size | column_count | index_count | check_constraint_count | sql |
+---------------+--------------+-------------+------------+----------------+-----------+---------+------+----------+-----------+-----------------+----------------+--------------+-------------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| memory | 570 | inventory | 2053 | products | 2055 | NULL | {} | false | false | true | 3 | 4 | 1 | 0 | CREATE TABLE inventory.products(product_id INTEGER PRIMARY KEY, product_name VARCHAR NOT NULL, price DECIMAL(8,2), stock_quantity INTEGER DEFAULT(0)); |
| memory | 570 | main | 572 | customers | 2033 | NULL | {} | false | false | true | 3 | 4 | 2 | 0 | CREATE TABLE customers(id INTEGER PRIMARY KEY, "name" VARCHAR NOT NULL, email VARCHAR UNIQUE, created_at TIMESTAMP DEFAULT(CURRENT_TIMESTAMP)); |
| memory | 570 | main | 572 | orders | 2032 | NULL | {} | false | false | true | 3 | 5 | 2 | 1 | CREATE TABLE orders(order_id INTEGER PRIMARY KEY, customer_id INTEGER, order_date DATE NOT NULL, total_amount DECIMAL(10,2), status VARCHAR DEFAULT('pending'), CHECK((total_amount > 0)), FOREIGN KEY (customer_id) REFERENCES customers(id)); |
| temp | 1970 | main | 1971 | temp_analytics | 2047 | NULL | {} | false | true | false | 2 | 3 | 0 | 0 | CREATE TABLE temp_analytics(metric_name VARCHAR, metric_value DOUBLE, recorded_at TIMESTAMP); |
+---------------+--------------+-------------+------------+----------------+-----------+---------+------+----------+-----------+-----------------+----------------+--------------+-------------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
It returns quite a few columns. We can always refine the list to just a handful of columns that are usually of interest:
SELECT
database_name,
schema_name,
table_name,
temporary,
has_primary_key,
column_count
FROM duckdb_tables()
ORDER BY schema_name, table_name;Output:
+---------------+-------------+----------------+-----------+-----------------+--------------+
| database_name | schema_name | table_name | temporary | has_primary_key | column_count |
+---------------+-------------+----------------+-----------+-----------------+--------------+
| memory | inventory | products | false | true | 4 |
| memory | main | customers | false | true | 4 |
| memory | main | orders | false | true | 5 |
| temp | main | temp_analytics | true | false | 3 |
+---------------+-------------+----------------+-----------+-----------------+--------------+
Find Tables with Primary Keys
The function returns a has_primary_key column that returns a Boolean value. It returns true if this table object defines a primary key.
Therefore we can use duckdb_tables() to find all tables that have primary keys:
SELECT
schema_name,
table_name,
column_count,
index_count
FROM duckdb_tables()
WHERE has_primary_key = true
ORDER BY table_name;Output:
+-------------+------------+--------------+-------------+
| schema_name | table_name | column_count | index_count |
+-------------+------------+--------------+-------------+
| main | customers | 4 | 2 |
| main | orders | 5 | 2 |
| inventory | products | 4 | 1 |
+-------------+------------+--------------+-------------+
Identify Temporary Tables
We can also use the function to find out if there are any temporary tables. That’s because it returns a temporary column (which is a Boolean value indicating whether this is a temporary table). So we can search for all tables that contain true in their temporary column:
SELECT
table_name,
column_count,
estimated_size
FROM duckdb_tables()
WHERE temporary = true;Output:
+----------------+--------------+----------------+
| table_name | column_count | estimated_size |
+----------------+--------------+----------------+
| temp_analytics | 3 | 2 |
+----------------+--------------+----------------+
Tables by Schema
We can use the function to find out how many tables are in each schema:
SELECT
schema_name,
COUNT(*) as table_count,
SUM(column_count) as total_columns
FROM duckdb_tables()
WHERE internal = false
GROUP BY schema_name
ORDER BY schema_name;Output:
+-------------+-------------+---------------+
| schema_name | table_count | total_columns |
+-------------+-------------+---------------+
| inventory | 1 | 4 |
| main | 3 | 12 |
+-------------+-------------+---------------+
Table Size Analysis
We can analyze table size as well as other factors, such as column count, index count, etc:
SELECT
table_name,
estimated_size,
column_count,
index_count,
check_constraint_count,
CASE
WHEN estimated_size > 1000000 THEN 'Large'
WHEN estimated_size > 100000 THEN 'Medium'
ELSE 'Small'
END as size_category
FROM duckdb_tables()
WHERE internal = false
ORDER BY estimated_size DESC;Output:
+----------------+----------------+--------------+-------------+------------------------+---------------+
| table_name | estimated_size | column_count | index_count | check_constraint_count | size_category |
+----------------+----------------+--------------+-------------+------------------------+---------------+
| products | 3 | 4 | 1 | 0 | Small |
| customers | 3 | 4 | 2 | 0 | Small |
| orders | 3 | 5 | 2 | 1 | Small |
| temp_analytics | 2 | 3 | 0 | 0 | Small |
+----------------+----------------+--------------+-------------+------------------------+---------------+
Find Tables with Constraints
This query returns tables with check constraints:
SELECT
schema_name,
table_name,
check_constraint_count,
has_primary_key
FROM duckdb_tables()
WHERE check_constraint_count > 0
ORDER BY check_constraint_count DESC;Output:
+-------------+------------+------------------------+-----------------+
| schema_name | table_name | check_constraint_count | has_primary_key |
+-------------+------------+------------------------+-----------------+
| main | orders | 1 | true |
+-------------+------------+------------------------+-----------------+
We only have one table with a check constraint. This is also shown in our previous query, which included a column showing how many check constraints each table has (only the orders table had 1 in this column – all other tables had 0).
Database Schema Overview
Let’s get a basic overview of the database structure:
SELECT
schema_name,
table_name,
column_count,
has_primary_key,
index_count,
temporary,
ROUND(estimated_size / 1024.0, 2) as size_kb
FROM duckdb_tables()
WHERE internal = false
ORDER BY schema_name, table_name;Output:
+-------------+----------------+--------------+-----------------+-------------+-----------+---------+
| schema_name | table_name | column_count | has_primary_key | index_count | temporary | size_kb |
+-------------+----------------+--------------+-----------------+-------------+-----------+---------+
| inventory | products | 4 | true | 1 | false | 0.0 |
| main | customers | 4 | true | 2 | false | 0.0 |
| main | orders | 5 | true | 2 | false | 0.0 |
| main | temp_analytics | 3 | false | 0 | true | 0.0 |
+-------------+----------------+--------------+-----------------+-------------+-----------+---------+
Table Creation SQL
The sql column contains the SQL code required to recreate the table. So we can use this column to generate scripts to do just that:
SELECT
table_name,
sql
FROM duckdb_tables()
WHERE table_name IN ('customers', 'orders')
ORDER BY table_name;Output:
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| table_name | sql |
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| customers | CREATE TABLE customers(id INTEGER PRIMARY KEY, "name" VARCHAR NOT NULL, email VARCHAR UNIQUE, created_at TIMESTAMP DEFAULT(CURRENT_TIMESTAMP)); |
| orders | CREATE TABLE orders(order_id INTEGER PRIMARY KEY, customer_id INTEGER, order_date DATE NOT NULL, total_amount DECIMAL(10,2), status VARCHAR DEFAULT('pending'), CHECK((total_amount > 0)), FOREIGN KEY (customer_id) REFERENCES customers(id)); |
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Database Health Check
We can use duckdb_tables() to provide a simple aggregate report of the database structure:
SELECT
COUNT(*) as total_tables,
COUNT(CASE WHEN has_primary_key THEN 1 END) as tables_with_pk,
COUNT(CASE WHEN temporary THEN 1 END) as temporary_tables,
AVG(column_count) as avg_columns_per_table,
SUM(estimated_size) as total_estimated_size
FROM duckdb_tables()
WHERE internal = false;Output:
+--------------+----------------+------------------+-----------------------+----------------------+
| total_tables | tables_with_pk | temporary_tables | avg_columns_per_table | total_estimated_size |
+--------------+----------------+------------------+-----------------------+----------------------+
| 4 | 3 | 1 | 4.0 | 11 |
+--------------+----------------+------------------+-----------------------+----------------------+