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.
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
| Command | Returns | Use it when |
|---|---|---|
.schema products | The exact CREATE TABLE text, plus CREATE INDEX statements for that table | You'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, pk | You need columns in code or a GUI query window |
PRAGMA table_xinfo(products) | The same, plus generated columns and a hidden flag | The 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_infohides generated columns. Our table had 7 columns;PRAGMA table_inforeturned 5.table_xinforeturned all 7, withhidden= 2 for a VIRTUAL generated column and 3 for a STORED one. Python'scursor.descriptionforSELECT *also listed all 7.- The declared type doesn't describe the data. In a column declared
INTwe stored'7 units', andtypeof(qty)returnedtext. A'12.50'string in aNUMERICcolumn was converted and stored as the real number 12.5. Check real types withSELECT typeof(col), COUNT(*) FROM t GROUP BY 1. - A TEXT primary key allowed NULLs, twice. In an ordinary (rowid) table,
code TEXT PRIMARY KEYaccepted two rows withNULLcodes, andtable_inforeportednotnull= 0. This is a long-standing SQLite quirk. AddNOT NULLexplicitly, or use aWITHOUT ROWIDtable, where the same column showednotnull= 1. - STRICT tables enforce the declared types. Inserting
'7 units'into an INTEGER column of a STRICT table failed withcannot 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|3From 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.