How to Get Column Names in SQLite Database

To get a table’s column names in SQLite, run PRAGMA table_info(table_name) and read the name field from each returned row. Replace table_name with the table’s name. The same metadata is available to Python through sqlite3; for a names-only SQL query, use the pragma_table_info table-valued function when your SQLite runtime provides it.

Get column names with PRAGMA table_info

PRAGMA table_info returns a row for each ordinary column. Its name field contains the column name, type contains the declared type, notnull indicates whether the column has a NOT NULL constraint, dflt_value contains its default expression (or NULL when there is none), and pk indicates primary-key participation. The cid field is the column’s rank within the table, starting at zero. This result is not a complete inventory when a table has generated or hidden columns: use PRAGMA table_xinfo to include those columns as well. Choose the pragma based on whether you need the ordinary table definition or every column SQLite reports.

This self-contained Python example creates a small table in memory, retrieves the metadata, and prints the names. The table name is a SQL identifier, so it is written directly in the PRAGMA statement rather than passed as a value parameter. It prints the column names returned by the example table.

import sqlite3

with sqlite3.connect(":memory:") as connection:
    connection.execute(
        "CREATE TABLE people (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER)"
    )
    rows = connection.execute("PRAGMA table_info(people)").fetchall()
    column_names = [row[1] for row in rows]
    print(column_names)

['id', 'name', 'age']

Use this approach when you need both names and column details, or when you want a method available through SQLite’s PRAGMA interface. If the table is in an attached database, specify its schema in the PRAGMA form, such as PRAGMA archive.table_info(people).

Query column names with the pragma_table_info table-valued function

When the SQLite runtime provides this table-valued function, query only the names with SELECT name FROM pragma_table_info('people');. This returns one name per row rather than all the metadata fields. To inspect a table in an attached schema, pass its schema name as the function’s second argument, for example SELECT name FROM pragma_table_info('people', 'archive');.

The Python version uses the same SQL and reads the first value in each result row. This standalone example creates the same people table, so its query matches the earlier example. It checks availability by handling SQLite’s operational error and otherwise prints the names:

import sqlite3

with sqlite3.connect(":memory:") as connection:
    connection.execute(
        "CREATE TABLE people (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER)"
    )
    try:
        rows = connection.execute(
            "SELECT name FROM pragma_table_info('people')"
        ).fetchall()
    except sqlite3.OperationalError as error:
        print(f"pragma_table_info is unavailable: {error}")
    else:
        print([row[0] for row in rows])

Availability depends on the SQLite runtime in use. If the function is unavailable, use PRAGMA table_info instead. The function form is useful when you want to combine column metadata with other SQL filters or joins.

Get column names in the SQLite command-line shell

In the SQLite command-line shell, open the database with sqlite3 path/to/database.db, then enter PRAGMA table_info(people);. Read the name column in the result to find the column names; the other fields provide the metadata described above. For a more readable display, enter .headers on and .mode column first. These dot commands change how the shell displays query results; they are shell settings, not SQL, and they do not retrieve the metadata themselves.