Shape / inspect · All operations

List columns and types

List columns, types, metadata.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

Shape of the result
a table describing the input rather than holding its rows
Level
one clause of SQL

The DuckDB step

Columns and types

It reads the orders table the proofs share. Create it first:

SQL
CREATE TABLE orders AS SELECT * FROM (VALUES
  (1,  'Alice Ng',      'north', DATE '2026-01-03', 120.00, 'widget', 'a|b',   '{"channel":"web","items":[1,2]}'),
  (2,  'alice ng',      'north', DATE '2026-01-05',  80.50, 'gadget', 'b',     '{"channel":"shop","items":[3]}'),
  (3,  'Bob  Okafor',   'south', DATE '2026-01-05', 300.00, 'widget', 'a|c',   '{"channel":"web","items":[4,5,6]}'),
  (4,  'Carla Díaz',    'east',  DATE '2026-01-09',  NULL,  'gizmo',  NULL,    '{"channel":"web","items":[]}'),
  (5,  'Bob Okafor',    'south', DATE '2026-01-12',  45.25, 'gadget', 'c',     '{"channel":"phone","items":[7]}'),
  (6,  'Dev Patel',     'west',  DATE '2026-01-12', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (7,  'Carla Diaz',    'east',  DATE '2026-01-20',  99.99, 'gizmo',  'b|c',   '{"channel":"shop","items":[10]}'),
  (8,  'Dev Patel',     'west',  DATE '2026-02-02', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (9,  'Eve Larsen',    NULL,    DATE '2026-02-03',  15.00, 'gadget', NULL,    '{"channel":"web","items":[11]}'),
  (10, 'Alice Ng',      'north', DATE '2026-02-10', 220.00, 'gizmo',  'a|b|c', '{"channel":"phone","items":[12,13]}')
) t(order_id, customer, region, order_date, amount, product, tags, meta);

Then run the statement:

SQL
CREATE TEMP TABLE result AS
SELECT column_name, column_type FROM (DESCRIBE orders);
SELECT * FROM result;
SELECT CASE WHEN ((SELECT count(*) = 8 AND max(column_type) FILTER (WHERE column_name = 'order_date') = 'DATE' FROM result)) THEN 'as expected' ELSE error('the schema is not eight columns with order_date a DATE') END AS verdict;
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “the schema is not eight columns with order_date a DATE”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

Variants

Describe a table's columns

List the name, type and other properties of each column of a table, query or stream.

Count rows and columns

Report how many rows and columns a table has.

Check that a table or column exists

Test whether a named table or column exists, as a flag or a pass or fail.

Other metadata

Read metadata no other variant covers, such as a model's expected inputs or a dataset's descriptive information.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

Alteryx Designer (3)
  • Allocate Metainforead 2026-09-27

    Return descriptive information about the installed Allocate datasets

  • Behavior Metainforead 2026-09-27

    Return descriptive information about installed Behavior Analysis datasets

  • Field Inforead 2026-09-27

    Return name, type, size and source of each field as rows

Apache DataFusion (8)
Apache Hop (8)
  • Column existsread 2026-09-27

    Flag whether a named column exists in a database table

  • Columns exist in a tableread 2026-09-27

    Succeed only when listed columns exist in a database table

  • File metadataread 2026-09-27

    Scan a text file to detect its delimiter, enclosure, encoding and field types

  • Get table namesread 2026-09-27

    List the schemas, tables, views, procedures and synonyms of a database connection

  • JDBC Metadataread 2026-09-27

    Fetch catalogs, schemas, tables, columns and keys through JDBC metadata calls

  • Metadata structure of streamread 2026-09-27

    Emit one row per input field with name, type, length, precision and origin

  • Table Existsread 2026-09-27

    Flag whether the table named in each row exists in a database

  • Table existsread 2026-09-27

    Succeed when a named table exists in a database

Apache NiFi (3)
  • ExtractAvroMetadataread 2026-09-27

    Extract metadata from the header of an Avro data file

  • ExtractRecordSchemaread 2026-09-27

    Extract the record schema with a record reader and write it to an attribute

  • IdentifyMimeTyperead 2026-09-27

    Detect the MIME type of content and write it to the mime.type attribute

Apache SeaTunnel (2)
  • TableFilterread 2026-09-27

    Keep or drop whole tables of a multi-table stream by database, schema and table name patterns

  • TableRenameread 2026-09-27

    Rename tables of a multi-table stream by case conversion, prefix, suffix or regex

AWS Glue (2)
  • printSchemaread 2026-09-27

    Print the schema of the frame

  • schemaread 2026-09-27

    Schema of the frame

dbt-utils (3)
DuckDB (21)
  • DESCRIBEread 2026-09-27

    Return the columns and types of a table or query

  • SHOW and SHOW DATABASESread 2026-09-27

    List tables, databases, or a table's columns

  • duckdb_columnsread 2026-09-27

    duckdb_columns table function

  • duckdb_constraintsread 2026-09-27

    duckdb_constraints table function

  • duckdb_coordinate_systemsread 2026-09-27

    duckdb_coordinate_systems table function

  • duckdb_databasesread 2026-09-27

    duckdb_databases table function

  • duckdb_dependenciesread 2026-09-27

    duckdb_dependencies table function

  • duckdb_indexesread 2026-09-27

    duckdb_indexes table function

  • duckdb_schemasread 2026-09-27

    duckdb_schemas table function

  • duckdb_sequencesread 2026-09-27

    duckdb_sequences table function

  • duckdb_tablesread 2026-09-27

    duckdb_tables table function

  • duckdb_typesread 2026-09-27

    duckdb_types table function

  • duckdb_viewsread 2026-09-27

    duckdb_views table function

  • parquet_bloom_proberead 2026-09-27

    parquet_bloom_probe table function

  • parquet_file_metadataread 2026-09-27

    parquet_file_metadata table function

  • parquet_full_metadataread 2026-09-27

    parquet_full_metadata table function

  • parquet_kv_metadataread 2026-09-27

    parquet_kv_metadata table function

  • parquet_metadataread 2026-09-27

    parquet_metadata table function

  • parquet_schemaread 2026-09-27

    parquet_schema table function

  • pragma_showread 2026-09-27

    pragma_show table function

  • pragma_table_inforead 2026-09-27

    pragma_table_info table function

Duckle (1)
  • Describeread 2026-09-25

    column names and types of the input

EasyMorph (1)
  • Table metadataread 2026-09-27

    Output column names, row count and other metadata of the current table

Ibis (1)
  • TypeOfread 2026-09-27

    Report the backend's own type name for an expression

KNIME Analytics Platform (5)
  • Column Name Extractorread 2026-09-27

    Output the column names of a table as a row or a column of strings

  • DB Data Spec Extractorread 2026-09-27

    List the column names and types of a DB data port

  • Extract Colorread 2026-09-27

    List the RGB and alpha values a colour model assigns, one row per colour

  • Extract Table Dimensionread 2026-09-27

    Output the number of rows and columns of a table as data and flow variables

  • Extract Table Specread 2026-09-27

    Output column names, types and domain metadata of a table

Pentaho Data Integration (6)
Polars (5)
  • DataFrame attributes (family)read 2026-09-27

    7 attributes: columns, dtypes, schema, shape, height, width, flags, plus collect_schema; report names, types and size

  • DataType expressions (group)read 2026-09-27

    9 methods: inner_dtype, matches, to_signed_integer, wrap_in_list, default_value and others; compute on data types lazily

  • read_ipc_schemaread 2026-09-27

    Read the schema of an Arrow IPC file without its data

  • read_parquet_metadataread 2026-09-27

    Read the custom file-level metadata of a Parquet file without its data

  • read_parquet_schemaread 2026-09-27

    Read the schema of a Parquet file without its data

Power Query M (8)
SQLMesh (1)
  • EXTERNALread 2026-09-27

    Hold column and type metadata for a table managed outside the project; never run

subtoken (1)
  • pragma_hnsw_index_inforead 2026-09-27

    Table function listing each HNSW index: table, metric, dimensions, count, memory, levels

Talend (1)
VisiData (6)
  • columns-allread 2026-09-23

    Open Columns Sheet for visible columns of all sheets on the stack

  • columns-selectedread 2026-09-23

    Open Columns Sheet for visible columns of the selected sheets

  • columns-sheetread 2026-09-23

    Open Columns Sheet: one editable row per column of current sheet

  • dive-metadataread 2026-09-23

    Open attribute metadata of the HDF5 object in current row

  • sheets-allread 2026-09-23

    Open Sheets Sheet: one row per sheet opened this session

  • sheets-stackread 2026-09-23

    Open Sheets Stack: one row per active sheet; jump, join, reorder or close them

Yggdrasil Decision Forests (4)
  • DataSpecificationread 2026-09-27

    Protobuf describing each column: name, type, dictionary, statistics, missing count

  • Model accessors (group)read 2026-09-27

    8 read-only accessors: name, task, label, label_classes, input_features, input_feature_names, label_col_idx …

  • data_spec / set_data_specread 2026-09-27

    Read or replace the column names, semantics and dictionaries the model expects

  • extract_input_feature_namesread 2026-09-27

    List which of the model's input features are present in a given dataset

Background

In PostgreSQL, the system catalogs are the place where schema metadata, such as information about tables and columns, is stored.[1] In DuckDB, the DESCRIBE statement shows the schema of a table, view or query.[2]

When to use it

In DuckDB, DESCRIBE placed before a query shows the schema of the query's result, and DESCRIBE can be used as a subquery to create a table from the description.[3] Reach for a schema listing before writing a join, a filter or a cast, so the statement is written against the types a table really has rather than the ones its column names suggest. (Meridian's view)

In DuckDB's information_schema.columns, is_nullable is YES if the column is possibly nullable and NO if it is known not to be nullable.[4] Read a YES there as permission rather than evidence: a schema says what a column may hold, and only a query over the rows says what it does hold. (Meridian's view) In DuckDB, the estimated_size column of duckdb_tables() gives the estimated number of rows in a table.[5] Count the rows when the number has to be exact, and keep a catalogue's estimate for a quick sense of a table's size. (Meridian's view)

The information schema can be used as a source of the information that some databases make available through non-standard commands, such as the SHOW command of MySQL.[6] In PostgreSQL, the information schema views do not contain information about PostgreSQL-specific features, which have to be read from the system catalogs or other PostgreSQL-specific views.[7] Where one query has to run on more than one database, prefer the information schema's views to each system's own commands, and check first that every target supports them. (Meridian's view)

The method

Codd's twelve rules, proposed by Edgar F. Codd, set out what a database management system needs in order to be considered relational.[8] Rule 4 of Codd's twelve rules asks for a dynamic online catalog: the database description is held like ordinary data, so authorised users can query it in the same relational language.[8]

In PostgreSQL, the information schema is defined in the SQL standard and can be expected to be portable and remain stable, unlike the system catalogs, which are specific to PostgreSQL.[7] PostgreSQL's system catalogs are regular tables.[1] In DuckDB, the views in information_schema are SQL-standard views that describe the catalog entries of the database, and the implementation is based on PostgreSQL's information schema.[4] In DuckDB, information_schema.columns is implemented as a query on top of the duckdb_columns() table function, which also returns metadata about DuckDB's internal objects.[5] In DuckDB, information_schema.columns gives each column's ordinal position within its table, counting from 1.[4]

Compared with the alternatives

In DuckDB, the SUMMARIZE statement returns summary statistics for a table, view or query.[9] Run a schema listing to learn what a table is declared to hold, and a summary such as SUMMARIZE to learn what its rows contain; the two read well side by side. (Meridian's view) In DuckDB 2.0, using SHOW to describe a table, view or query is deprecated and prints a warning, and DESCRIBE is the statement to use instead.[10]

Sources

  1. [1]PostgreSQL: Documentation: 18: Chapter 52. System Catalogsdocumentation
  2. [2]DESCRIBE Statement – DuckDBdocumentation
  3. [3]Describe – DuckDBdocumentation
  4. [4]Information Schema – DuckDBdocumentation
  5. [5]DuckDB_% Metadata Functions – DuckDBdocumentation
  6. [6]Information schema - Wikipediasecondary source
  7. [7]PostgreSQL: Documentation: 18: Chapter 35. The Information Schemadocumentation
  8. [8]Codd's 12 rules - Wikipediasecondary source
  9. [9]SUMMARIZE Statement – DuckDBdocumentation
  10. [10]SHOW, SHOW DATABASES, and SHOW SCHEMAS Statements – DuckDBdocumentation

Found by dovetail