DuckDB offers a collection of table functions that provide metadata about the current database. These functions reside in the main schema and their names are prefixed with duckdb_.

The resultset returned by a duckdb_ table function may be used just like an ordinary table or view. For example, you can use a duckdb_ function call in the FROM clause of a SELECT statement, and you may refer to the columns of its returned resultset elsewhere in the statement, for example in the WHERE clause.

Table functions are still functions, and you should write parenthesis after the function name to call it to obtain its returned resultset:

SELECT * FROM duckdb_settings()

Alternatively, you may execute table functions also using the CALL-syntax:

CALL duckdb_settings()

In this case too, the parentheses are mandatory.

Note: For some of the duckdb_% functions, there is also a identically named view available, which also resides in the main schema. Typically, these views do a SELECT on the duckdb_ table function with the same name, while filtering out those objects that are marked as internal. We mention it here, because if you accidentally omit the parentheses in your duckdb_ table function call, you might still get a result, but from the identically named view.

Example:

-- duckdb_views table function: returns all views, including those marked internal
SELECT *
FROM   duckdb_views()
;
-- duckdb_views view: returns views that are not marked as internal
SELECT *
FROM   duckdb_views
;

duckdb_columns

The duckdb_columns() function provides metadata about the columns available in the DuckDB instance.

The information_schema.columns system view provides a more standardized way to obtain metadata about database columns, but the duckdb_columns function also returns metadata about DuckDB internal objects. (In fact, information_schema.columns is implemented as a query on top of duckdb_columns())

duckdb_constraints

The duckdb_constraints() function provides metadata about the constraints available in the DuckDB instance.

duckdb_databases

The duckdb_databases() function lists the databases that are accessible from within the current DuckDB process. Apart from the database associated at startup, the list also includes databases that were attached later on to the duckdb process

duckdb_dependencies

The duckdb_dependencies() function provides metadata about the dependencies available in the DuckDB instance.

duckdb_extensions

The duckdb_extensions() function provides metadata about the extensions available in the DuckDB instance.

duckdb_functions

The duckdb_functions() function provides metadata about the functions available in the DuckDB instance.

duckdb_indexes

The duckdb_indexes() function provides metadata about secondary indexes available in the DuckDB instance.

Note that duckdb_indexes only provides metadata about secondary indexes - i.e. those indexes created by explicit CREATE INDEX statements. Primary keys are maintained using indexes, but their details are included in the duckdb_constraints() function.

duckdb_keywords

The duckdb_keywords() function provides metadata about DuckDB's keywords and reserved words.

duckdb_schemas

The duckdb_schemas() function provides metadata about the schemas available in the DuckDB instance.

The information_schema.schemata system view provides a more standardized way to obtain metadata about database schemas.

duckdb_sequences

The duckdb_sequences() function provides metadata about the sequences available in the DuckDB instance.

Attributes like temporary, start_value etc. correspond to the various options available in the create sequence statement and are documented there in full. Note that the attributes will always be filled out in the duckdb_sequences resultset, even if they were not explicitly specified in the create sequence statement. Note1: The column name last_value suggests that it contains the last value that was drawn from the sequence, but that is not the case. It's either null if a value was never drawn from the sequence, or 1 (when there was a value drawn, ever, from the sequence). Note2: If the sequence cycles, then the sequence will start over from the boundary of its range, not necessarily from the value specified as start value.

duckdb_settings

The duckdb_settings() function provides metadata about the settings available in the DuckDB instance.

The various settings are described in the configuration page.

duckdb_tables

The duckdb_tables() function provides metadata about the base tables available in the DuckDB instance.

The information_schema.tables system view provides a more standardized way to obtain metadata about database tables that also includes views. But the resultset returned by duckdb_tables contains a few columns that are not included in information_schema.tables.

duckdb_types

The duckdb_types() function provides metadata about the data types available in the DuckDB instance.

duckdb_views

The duckdb_views() function provides metadata about the views available in the DuckDB instance.

The information_schema.tables system view provides a more standardized way to obtain metadata about database views that also includes base tables. But the resultset returned by duckdb_views contains also definitions of internal view objects as well as a few columns that are not included in information_schema.tables.