SQLite Describe a Table

SQLite does not provide a DESCRIBE command. To inspect a table, use the .schema command in the SQLite command-line shell to read its CREATE TABLE statement, PRAGMA table_info to see column metadata, or a query against sqlite_schema to retrieve the stored definition. These methods answer related but different questions: the schema text shows how the table was defined, while the PRAGMA returns one row per column.

How to Describe a Table in SQLite

Choose a method based on the information you need. Use .schema when you want the SQL definition, PRAGMA table_info when you want a structured list of columns and their properties, and sqlite_schema when you need to find a definition through SQL. The SQLite command-line shell’s .tables command can help you check the exact table name first.

Use .schema in the SQLite Command-Line Shell

.schema is a dot-command understood by the SQLite command-line shell; it is not SQL and cannot be sent as a SQL statement through a database library. To display one table’s definition, enter .schema employee. The shell prints the corresponding CREATE TABLE statement, including declared columns and constraints. To list available tables, enter .tables. For indented schema output, use .schema --indent.

If .schema employee prints nothing, do not conclude from that alone that the table is absent. Check the spelling and capitalization against .tables, and confirm that the shell is connected to the database file you intended to open. You can also query sqlite_schema as shown below. An empty lookup can result from a name mismatch or from inspecting a different database.

Use PRAGMA table_info and PRAGMA table_xinfo

Run PRAGMA table_info(employee); to get a row for each ordinary table column. Its result includes the column’s position (cid), name, declared type, whether it is required (notnull), its default value (dflt_value), and whether it participates in a primary key (pk). This is structured metadata, rather than the original CREATE TABLE text; it does not show every table constraint in the same form as .schema.

Use PRAGMA table_xinfo(employee); when you also need hidden or generated columns, and only when the SQLite build you are using supports it. It returns the table_info columns plus a hidden field that indicates the column’s status. If the command is not supported in your build, use table_info for the columns it reports or consult the documentation for that build. No particular SQLite version boundary is assumed here.

Query sqlite_schema for a Table Definition

sqlite_schema stores schema entries for tables, indexes, views, and triggers. To retrieve a table definition with SQL, filter for the table’s name and object type:

The query is SELECT sql FROM sqlite_schema WHERE type = 'table' AND name = 'employee';.

The sql column contains the SQL text used to define the object, or NULL for some internal indexes. The older name sqlite_master is an alias for sqlite_schema; it is not a command that takes a table name. Do not modify schema catalog tables directly. SQLite maintains them as database objects change, and direct edits can damage the database.

Example: Inspect an Employee Table

This Python example creates a small in-memory database and inspects the employee table. It uses SQLite’s DB-API, so it works without shell dot-commands and keeps the database self-contained.

Run this example:

import sqlite3

connection = sqlite3.connect(":memory:")
connection.execute(
    "CREATE TABLE employee ("
    "id INTEGER PRIMARY KEY, "
    "name TEXT NOT NULL, "
    "dept TEXT NOT NULL, "
    "salary TEXT NOT NULL)"
)

columns = connection.execute("PRAGMA table_info(employee)").fetchall()
for column in columns:
    print(column[1], column[2], "primary key=" + str(bool(column[5])))

definition = connection.execute(
    "SELECT sql FROM sqlite_schema WHERE type = ? AND name = ?",
    ("table", "employee"),
).fetchone()[0]
print(definition)
connection.close()

The PRAGMA rows identify each column and mark id as the primary key. The catalog query returns the table’s CREATE TABLE text. In a shell session, .schema employee is a convenient way to display that definition; use the PRAGMA when you need to process column properties programmatically.