Get Started Free

How to describe a table in SQLite

Show a SQLite table's columns, types, defaults and keys with .schema, PRAGMA table_info and table_xinfo, and see why declared types can lie.

Updated November 14, 2026 ยท Tested on SQLite 3.43.2 (sqlite3 shell), SQLite 3.45.3 (Python 3)

Quick Answer

SQLite has no DESCRIBE. To see the full CREATE statement, use .schema in the sqlite3 shell:

sqlite> .schema products

For one row per column that you can query from any client, use PRAGMA table_info:

PRAGMA table_info(products);

Three ways to describe a table

CommandReturnsUse it when
.schema productsThe exact CREATE TABLE text, plus CREATE INDEX statements for that tableYou're in the sqlite3 shell and want to read the definition
PRAGMA table_info(products)One row per column: cid, name, type, notnull, dflt_value, pkYou need columns in code or a GUI query window
PRAGMA table_xinfo(products)The same, plus generated columns and a hidden flagThe table has GENERATED ALWAYS columns
sqlite> .headers on
sqlite> PRAGMA table_info(products);
cid|name|type|notnull|dflt_value|pk
0|id|INTEGER|0||1
1|sku|TEXT|1||0
2|price|numeric|0|0|0
3|qty|INT|0||0
4|tags||0||0

pk is the position of the column in the primary key (1, 2, ...), or 0 if it isn't part of it. An empty type means the column was declared with no type at all, which SQLite allows.

DESCRIBE products; fails with Parse error: near "describe": syntax error.

In VisuaLeaf

Read a SQLite table's structure in VisuaLeaf

Open the .db file in VisuaLeaf, right-click a table and choose Tables Info. The Schema tab shows every column with its declared type, nullability, default and primary key, followed by the table's foreign keys and its indexes, with unique ones marked. Show DDL displays the full CREATE TABLE statement and its CREATE INDEX statements, much like .schema does in the shell.

The declared type is only half the story in SQLite, as the tests above show. To see what's actually stored, run this in the SQL editor next to it:

SELECT typeof(price) AS stored_as, COUNT(*) FROM products GROUP BY 1;

If a column holds text, integers and reals, the declared type won't tell you, but this query will. See Table Management.

Use table_info inside a SELECT

Every PRAGMA that returns rows also exists as a table-valued function, so you can filter and join it like a table:

-- just the names and types
SELECT name, type, "notnull", pk FROM pragma_table_info('products');

-- every column of every table
SELECT m.name AS table_name, p.name AS column_name, p.type
FROM sqlite_schema AS m
JOIN pragma_table_info(m.name) AS p
WHERE m.type = 'table'
ORDER BY m.name, p.cid;

notnull needs double quotes in a SELECT because NOTNULL is also an SQL keyword.

Indexes and foreign keys

table_info only describes columns. Ask for keys and indexes separately:

PRAGMA index_list(products);        -- indexes, including the automatic ones for UNIQUE
PRAGMA index_info('products_price'); -- columns in one index
PRAGMA foreign_key_list(line_items); -- referenced table, columns, ON DELETE / ON UPDATE

A UNIQUE column gets an automatic index named sqlite_autoindex_products_1, which appears in index_list but has no CREATE statement in .schema.

What we found testing this

We described a table with generated columns, a numeric column, an untyped column, a STRICT table and a WITHOUT ROWID table:

  • table_info hides generated columns. Our table had 7 columns; PRAGMA table_info returned 5. table_xinfo returned all 7, with hidden = 2 for a VIRTUAL generated column and 3 for a STORED one. Python's cursor.description for SELECT * also listed all 7.
  • The declared type doesn't describe the data. In a column declared INT we stored '7 units', and typeof(qty) returned text. A '12.50' string in a NUMERIC column was converted and stored as the real number 12.5. Check real types with SELECT typeof(col), COUNT(*) FROM t GROUP BY 1.
  • A TEXT primary key allowed NULLs, twice. In an ordinary (rowid) table, code TEXT PRIMARY KEY accepted two rows with NULL codes, and table_info reported notnull = 0. This is a long-standing SQLite quirk. Add NOT NULL explicitly, or use a WITHOUT ROWID table, where the same column showed notnull = 1.
  • STRICT tables enforce the declared types. Inserting '7 units' into an INTEGER column of a STRICT table failed with cannot store TEXT value in INTEGER column strict_t.n, while '7' was converted and stored as the integer 7.
sqlite> PRAGMA table_xinfo(products);
cid|name|type|notnull|dflt_value|pk|hidden
...
5|amount_cents|INTEGER|0||0|2
6|label|TEXT|0||0|3

From Python

import sqlite3

con = sqlite3.connect("shop.db")
for cid, name, type_, notnull, default, pk, hidden in con.execute(
        "SELECT * FROM pragma_table_xinfo(?)", ("products",)):
    print(name, type_ or "(no type)", "NOT NULL" if notnull else "", "PK" if pk else "")

Passing the table name as a parameter to pragma_table_xinfo(?) works. PRAGMA table_info(?) with a placeholder does not, because PRAGMA statements don't accept bound parameters.

Frequently Asked Questions

Is there a DESCRIBE command in SQLite?

No. Use .schema tablename in the sqlite3 shell, or PRAGMA table_info(tablename) from SQL.

How do I get the CREATE TABLE statement?

.schema tablename in the shell, or SELECT sql FROM sqlite_schema WHERE type = 'table' AND name = 'tablename' from any client.

Why is a generated column missing from PRAGMA table_info?

table_info skips hidden and generated columns. Use PRAGMA table_xinfo, which includes them and adds a hidden column.

Can I change a column type in SQLite?

Not in place. ALTER TABLE can add, rename and drop columns, but changing a type means creating a new table, copying the rows and renaming it.

Free Tools

VisuaLeaf for SQLite

Read SQLite table structure at a glance

VisuaLeaf's table management shows a SQLite table's columns, types, defaults, keys, triggers and constraints in a form, and gives you its full CREATE TABLE statement in one click.