SQLite Basics: Inspect Tables, Columns, and Indexes with SQL

Before changing an unfamiliar database, inspect its structure. SQLite exposes schema metadata through sqlite_schema and table-valued PRAGMA functions, so you can filter and order the results with ordinary SELECT queries.

Use SQLite 3.37.0 or later for every example here: table_list was added in that release. Table-valued PRAGMA functions themselves arrived in 3.16.0. The PRAGMA reference documents both interfaces. These scripts create their own toy schemas first; their subsequent inspection queries do not alter those schemas.

Prepare a scratch directory

Tested on Linux with Python 3.12.14 and SQLite 3.53.1. You need Bash, Python 3 with SQLite support, a text editor, and mktemp, rm, and rmdir. No additional Python packages are needed. Keep the same terminal open and save files as UTF-8.

start_dir="$PWD"
work_dir="$(mktemp -d)" || exit 1
cd -- "$work_dir" || exit 1

The standalone SQLite CLI was unavailable on the test machine. Save this runner as run-sql.py. It prints pipe-separated rows and displays missing values as NULL. It does not escape embedded pipes or newlines, so it is a teaching runner, not a general SQL shell or CSV exporter. Put each statement’s ending semicolon at the end of a line, as shown; do not put two complete statements on one line.

from pathlib import Path
import sqlite3
import sys

connection = sqlite3.connect(":memory:", isolation_level=None)
try:
    statement = ""
    for line in Path(sys.argv[1]).read_text(encoding="utf-8").splitlines(True):
        statement += line
        if sqlite3.complete_statement(statement):
            cursor = connection.execute(statement)
            for row in cursor:
                print("|".join("NULL" if value is None else str(value) for value in row))
            statement = ""
    if statement.strip():
        raise ValueError("Unfinished SQL statement")
finally:
    connection.close()

Each invocation starts with a fresh in-memory database. The examples are independent, and the toy data disappears when the connection closes.

1. Separate object names from schema locations

Save as list-schema-objects.sql:

CREATE TABLE tasks (
    id INTEGER PRIMARY KEY,
    project TEXT NOT NULL,
    done INTEGER NOT NULL CHECK (done IN (0, 1))
);
CREATE INDEX tasks_project ON tasks(project);
CREATE VIEW open_tasks AS SELECT id, project FROM tasks WHERE done = 0;
CREATE TEMP TABLE scratch (note TEXT);
SELECT type, name, tbl_name
FROM main.sqlite_schema
WHERE name NOT GLOB 'sqlite_*'
ORDER BY type, name;
SELECT schema, name, type, ncol, wr, strict
FROM pragma_table_list
WHERE name IN ('tasks', 'open_tasks', 'scratch')
ORDER BY schema, name;

Run:

python3 run-sql.py list-schema-objects.sql

Output:

index|tasks_project|tasks
table|tasks|tasks
view|open_tasks|open_tasks
main|open_tasks|view|2|0|0
main|tasks|table|3|0|0
temp|scratch|table|1|0|0

The first query is explicitly scoped to main. It lists the table, its index, and the view, but not the temporary scratch table. The second query shows each table or view’s schema name, column count, WITHOUT ROWID flag, and STRICT flag. All flags are zero in this fixture.

The index’s tbl_name is tasks because that is the table it indexes. The view’s tbl_name is its own name, not a list of its dependencies. Do not use that field as a general dependency graph. Filtering out sqlite_* names makes this a user-object inventory rather than a complete internal catalog.

2. Include generated columns in a column inventory

Save as inspect-generated-column-metadata.sql:

CREATE TABLE order_lines (
    id INTEGER PRIMARY KEY,
    quantity INTEGER NOT NULL,
    unit_cents INTEGER NOT NULL DEFAULT 0,
    line_cents INTEGER GENERATED ALWAYS AS (quantity * unit_cents) STORED
);
SELECT name, type, "notnull", dflt_value, pk, hidden
FROM pragma_table_xinfo('order_lines')
ORDER BY cid;
SELECT 'table_info columns', COUNT(*)
FROM pragma_table_info('order_lines');
SELECT 'table_xinfo columns', COUNT(*)
FROM pragma_table_xinfo('order_lines');

Run:

python3 run-sql.py inspect-generated-column-metadata.sql

Output:

id|INTEGER|0|NULL|1|0
quantity|INTEGER|1|NULL|0|0
unit_cents|INTEGER|1|0|0|0
line_cents|INTEGER|0|NULL|0|3
table_info columns|3
table_xinfo columns|4

table_xinfo includes the stored generated column that table_info omits. Its hidden value is 3; ordinary columns report 0. The default expression for unit_cents is 0, while NULL in that metadata field means no declared default.

The pk field identifies the primary-key position. Do not read the notnull flag in isolation: this rowid-alias INTEGER PRIMARY KEY reports 0 for that flag even though stored IDs are non-NULL. Schema interpretation needs the complete declaration and SQLite’s key rules. Selecting named result columns also avoids depending on a future PRAGMA column being added.

3. Distinguish declared indexes from key relationships

Save as inspect-indexes-and-foreign-keys.sql:

PRAGMA foreign_keys = ON;
CREATE TABLE teams (id INTEGER PRIMARY KEY);
CREATE TABLE people (
    id INTEGER PRIMARY KEY,
    team_id INTEGER REFERENCES teams(id) ON DELETE RESTRICT,
    code TEXT NOT NULL UNIQUE
);
CREATE INDEX people_team ON people(team_id);
SELECT name, "unique", origin, partial
FROM pragma_index_list('people')
ORDER BY name;
SELECT seqno, name FROM pragma_index_info('people_team') ORDER BY seqno;
SELECT "table", "from", "to", on_update, on_delete
FROM pragma_foreign_key_list('people')
ORDER BY id, seq;

Run:

python3 run-sql.py inspect-indexes-and-foreign-keys.sql

Output:

people_team|0|c|0
sqlite_autoindex_people_1|1|u|0
0|team_id
teams|team_id|id|NO ACTION|RESTRICT

people_team is an explicit, nonunique index. The automatic index enforces the UNIQUE code constraint. In origin, c means CREATE INDEX and u means a UNIQUE constraint; partial says whether the index has a WHERE predicate. index_info then confirms which column forms the explicit index key.

The final row describes the declared relationship from people.team_id to teams.id and its actions. It does not prove existing rows satisfy that relationship. Likewise, finding an index is not proof that a particular query uses it. Schema inspection, data validation, and query-plan inspection answer different questions.

Read the schema-table reference for object fields and stored CREATE statements. Never edit sqlite_schema directly as a shortcut for a migration: incorrect changes can corrupt the database. Use supported schema-changing statements after reviewing a backup and migration plan.

For a real inventory, qualify the intended schema and keep output ordering explicit. These in-memory examples inspect only definitions, not file health, data correctness, or performance.

Clean up

In the same terminal, remove only these example files and the empty scratch directory:

rm -- run-sql.py list-schema-objects.sql inspect-generated-column-metadata.sql inspect-indexes-and-foreign-keys.sql
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

Acknowledgements

This post was created with assistance from AI.