Pragmatic SQLite Administration

Table Info

Inspect Columns

SQLite exposes table metadata through pragma_table_info, including column order, type, nullability, and primary-key position.

Program

Play the script to choose a table and inspect its column metadata.

table_name
table_info_report.sql
Replay: real traced execution (multi-file project)
CREATE TABLE products (id INTEGER PRIMARY KEY, sku TEXT NOT NULL, price INTEGER);
CREATE TABLE order_items (order_id INTEGER, product_id INTEGER, quantity INTEGER NOT NULL);
WITH params(table_name) AS (VALUES ('products')) SELECT name, type, "notnull" AS required, pk FROM pragma_table_info((SELECT table_name FROM params)) ORDER BY cid;

CREATE TABLE products (id INTEGER PRIMARY KEY, sku TEXT NOT NULL, price INTEGER);
CREATE TABLE order_items (order_id INTEGER, product_id INTEGER, quantity INTEGER NOT NULL);
WITH params(table_name) AS (VALUES ('order_items')) SELECT name, type, "notnull" AS required, pk FROM pragma_table_info((SELECT table_name FROM params)) ORDER BY cid;

  1. tables ← 1 row

    1CREATE TABLE products (id INTEGER PRIMARY KEY, sku TEXT NOT NULL, price INTEGER);2CREATE TABLE order_items (order_id INTEGER, product_id INTEGER, quantity INTEGER NOT NULL);
    values this step1 rowtables

  2. tables ← 2 rows

    1CREATE TABLE products (id INTEGER PRIMARY KEY, sku TEXT NOT NULL, price INTEGER);2CREATE TABLE order_items (order_id INTEGER, product_id INTEGER, quantity INTEGER NOT NULL);3WITH params(table_name) AS (VALUES ('products')) SELECT name, type, "notnull" AS required, pk FROM pragma_table_info((SELECT table_name FROM params)) ORDER BY cid;
    values this step2 rowstables

  3. result ← 3 rows

    2CREATE TABLE order_items (order_id INTEGER, product_id INTEGER, quantity INTEGER NOT NULL);3WITH params(table_name) AS (VALUES ('products')) SELECT name, type, "notnull" AS required, pk FROM pragma_table_info((SELECT table_name FROM params)) ORDER BY cid;
    values this step3 rowsresult

  1. tables ← 1 row

    1CREATE TABLE products (id INTEGER PRIMARY KEY, sku TEXT NOT NULL, price INTEGER);2CREATE TABLE order_items (order_id INTEGER, product_id INTEGER, quantity INTEGER NOT NULL);
    values this step1 rowtables

  2. tables ← 2 rows

    1CREATE TABLE products (id INTEGER PRIMARY KEY, sku TEXT NOT NULL, price INTEGER);2CREATE TABLE order_items (order_id INTEGER, product_id INTEGER, quantity INTEGER NOT NULL);3WITH params(table_name) AS (VALUES ('order_items')) SELECT name, type, "notnull" AS required, pk FROM pragma_table_info((SELECT table_name FROM params)) ORDER BY cid;
    values this step2 rowstables

  3. result ← 3 rows

    2CREATE TABLE order_items (order_id INTEGER, product_id INTEGER, quantity INTEGER NOT NULL);3WITH params(table_name) AS (VALUES ('order_items')) SELECT name, type, "notnull" AS required, pk FROM pragma_table_info((SELECT table_name FROM params)) ORDER BY cid;
    values this step3 rowsresult
pragma_table_info `pragma_table_info` returns one row per column.
required flag The `notnull` field shows which columns reject NULL values.
primary key `pk` marks primary-key columns and their key order.