Pragmatic SQLite Administration

Catalog Inventory

List Schema Objects

SQLite stores schema objects in sqlite_master, which makes lightweight catalog checks possible.

Program

Play the script to choose an object type and inspect the tables or indexes in the catalog.

object_type
catalog_object_inventory.sql
Replay: real traced execution (multi-file project)
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total INTEGER);
CREATE INDEX idx_orders_customer ON orders(customer_id);
WITH params(object_type) AS (VALUES ('table')) SELECT type, name, tbl_name FROM sqlite_master WHERE type = (SELECT object_type FROM params) ORDER BY name;

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total INTEGER);
CREATE INDEX idx_orders_customer ON orders(customer_id);
WITH params(object_type) AS (VALUES ('index')) SELECT type, name, tbl_name FROM sqlite_master WHERE type = (SELECT object_type FROM params) ORDER BY name;

  1. tables ← 1 row

    1CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);2CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total INTEGER);
    values this step1 rowtables

  2. tables ← 2 rows

    1CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);2CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total INTEGER);3CREATE INDEX idx_orders_customer ON orders(customer_id);
    values this step2 rowstables

  3. indexes ← 1 row

    2CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total INTEGER);3CREATE INDEX idx_orders_customer ON orders(customer_id);4WITH params(object_type) AS (VALUES ('table')) SELECT type, name, tbl_name FROM sqlite_master WHERE type = (SELECT object_type FROM params) ORDER BY name;
    values this step1 rowindexes

  4. result ← 2 rows

    3CREATE INDEX idx_orders_customer ON orders(customer_id);4WITH params(object_type) AS (VALUES ('table')) SELECT type, name, tbl_name FROM sqlite_master WHERE type = (SELECT object_type FROM params) ORDER BY name;
    values this step2 rowsresult

  1. tables ← 1 row

    1CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);2CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total INTEGER);
    values this step1 rowtables

  2. tables ← 2 rows

    1CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);2CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total INTEGER);3CREATE INDEX idx_orders_customer ON orders(customer_id);
    values this step2 rowstables

  3. indexes ← 1 row

    2CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total INTEGER);3CREATE INDEX idx_orders_customer ON orders(customer_id);4WITH params(object_type) AS (VALUES ('index')) SELECT type, name, tbl_name FROM sqlite_master WHERE type = (SELECT object_type FROM params) ORDER BY name;
    values this step1 rowindexes

  4. result ← 1 row

    3CREATE INDEX idx_orders_customer ON orders(customer_id);4WITH params(object_type) AS (VALUES ('index')) SELECT type, name, tbl_name FROM sqlite_master WHERE type = (SELECT object_type FROM params) ORDER BY name;
    values this step1 rowresult
sqlite_master `sqlite_master` is SQLite's catalog table for schema objects.
object type Filtering by `type` separates tables, indexes, views, and triggers.
stable inventory Catalog queries are useful for small migration or smoke-check scripts.