SQLite Describe Table: Show Columns and Schema (Tested)

Ninad Pathak

October 7, 2026

Seeing a table’s columns in SQLite takes a PRAGMA or a dot-command, since there’s no DESCRIBE. Either one shows each column’s type and constraints, and in this article I’ll walk you through every way to see a table’s columns and schema, in the shell and from code.

TLDR

SQLite has no DESCRIBE. In the shell, .schema visitors prints the CREATE TABLE statement.
PRAGMA table_info(visitors); lists one row per column, with its type and constraints.

In a query, use SELECT name FROM pragma_table_info('visitors'); to get only the column names.
pragma_table_xinfo also shows generated columns, which table_info leaves out.
The full schema of every table is in the sqlite_schema table (also called sqlite_master).

What does “describe table” mean in SQLite?

Describing a table means listing its columns and their definitions. MySQL has DESCRIBE and SHOW COLUMNS for this. SQLite doesn’t, and gives you these instead:

Method	Where it works	Shows
`.schema table`	`sqlite3` shell only	The `CREATE TABLE` statement and its indexes
`PRAGMA table_info(table)`	Any SQLite connection	One row per column
`pragma_table_info('table')`	Inside a `SELECT`	The same rows, as a table you can filter
`sqlite_schema`	Inside a `SELECT`	The stored SQL for every table, index, view and trigger

Describe table syntax

.schema table_name                                   -- sqlite3 shell
PRAGMA table_info(table_name);                       -- any connection
SELECT * FROM pragma_table_info('table_name');       -- inside SQL
SELECT sql FROM sqlite_schema WHERE name = 'table_name';

PRAGMA table_info returns these columns (PRAGMA table_info):

Column	Meaning
`cid`	The column’s position, starting at 0
`name`	Column name
`type`	Declared type, or empty if none
`notnull`	1 if the column is `NOT NULL`
`dflt_value`	The default value, as SQL text
`pk`	0 if not in the primary key, otherwise its position in the key

How to describe a table in SQLite

To show you each method, I will use a small analytics database with a visitors table and a visits table:

CREATE TABLE visitors (
  visitor_id    INTEGER PRIMARY KEY,
  email         TEXT NOT NULL UNIQUE,
  country       TEXT DEFAULT 'IN',
  pages_visited INTEGER NOT NULL DEFAULT 0,
  first_seen    TEXT,
  domain        TEXT GENERATED ALWAYS AS (substr(email, instr(email, '@') + 1)) VIRTUAL
);
CREATE TABLE visits (
  id         INTEGER PRIMARY KEY,
  visitor_id INTEGER NOT NULL REFERENCES visitors(visitor_id) ON DELETE CASCADE,
  url        TEXT NOT NULL
);
CREATE INDEX idx_visitors_country ON visitors(country);

Step 1: Try DESCRIBE

sqlite> DESCRIBE visitors;
Error: in prepare, near "DESCRIBE": syntax error
  DESCRIBE visitors;
  ^--- error here

Step 2: Print the table’s definition with .schema

In the sqlite3 shell, .schema followed by a table name prints its CREATE TABLE statement and the indexes on it:

sqlite> .schema visitors
CREATE TABLE visitors (
  visitor_id    INTEGER PRIMARY KEY,
  email         TEXT NOT NULL UNIQUE,
  country       TEXT DEFAULT 'IN',
  pages_visited INTEGER NOT NULL DEFAULT 0,
  first_seen    TEXT,
  domain        TEXT GENERATED ALWAYS AS (substr(email, instr(email, '@') + 1)) VIRTUAL
);
CREATE INDEX idx_visitors_country ON visitors(country);

Step 3: List the columns with PRAGMA table_info

sqlite> PRAGMA table_info(visitors);
cid  name           type     notnull  dflt_value  pk
---  -------------  -------  -------  ----------  --
0    visitor_id     INTEGER  0                    1
1    email          TEXT     1                    0
2    country        TEXT     0        'IN'        0
3    pages_visited  INTEGER  1        0           0
4    first_seen     TEXT     0                    0

The visitor_id column shows notnull 0 even though it’s the primary key. An INTEGER PRIMARY KEY is the table’s rowid, and inserting NULL into it assigns the next ID instead. The domain column is missing, because table_info skips generated columns.

Examples

Each example below uses the same analytics.db.

Query the columns like a table

The pragma_table_info() function form works anywhere a table does, so you can pick and filter columns. Table-valued pragma functions arrived in SQLite 3.16.0 (PRAGMA functions):

sqlite> SELECT name, type, "notnull", dflt_value, pk FROM pragma_table_info('visitors');
name           type     notnull  dflt_value  pk
-------------  -------  -------  ----------  --
visitor_id     INTEGER  0                    1
email          TEXT     1                    0
country        TEXT     0        'IN'        0
pages_visited  INTEGER  1        0           0
first_seen     TEXT     0                    0

Get only the column names

sqlite> SELECT group_concat(name, ', ') AS columns FROM pragma_table_info('visitors');
columns
-----------------------------------------------------
visitor_id, email, country, pages_visited, first_seen

Check whether a column exists

sqlite> SELECT count(*) AS has_country FROM pragma_table_info('visitors') WHERE name = 'country';
has_country
-----------
1

Include generated and hidden columns

table_xinfo adds a hidden column, which is non-zero for generated columns (PRAGMA table_xinfo):

sqlite> SELECT name, type, hidden FROM pragma_table_xinfo('visitors');
name           type     hidden
-------------  -------  ------
visitor_id     INTEGER  0
email          TEXT     0
country        TEXT     0
pages_visited  INTEGER  0
first_seen     TEXT     0
domain         TEXT     2

Show the schema of every table

.schema with no table name prints the whole database:

sqlite> .schema
CREATE TABLE visitors (
  visitor_id    INTEGER PRIMARY KEY,
  email         TEXT NOT NULL UNIQUE,
  country       TEXT DEFAULT 'IN',
  pages_visited INTEGER NOT NULL DEFAULT 0,
  first_seen    TEXT,
  domain        TEXT GENERATED ALWAYS AS (substr(email, instr(email, '@') + 1)) VIRTUAL
);
CREATE TABLE visits (
  id         INTEGER PRIMARY KEY,
  visitor_id INTEGER NOT NULL REFERENCES visitors(visitor_id) ON DELETE CASCADE,
  url        TEXT NOT NULL
);
CREATE INDEX idx_visitors_country ON visitors(country);

From SQL, the same text is in sqlite_schema, which has one row per schema object. sqlite_master is an older name for the same table (The schema table):

sqlite> SELECT type, name, tbl_name FROM sqlite_schema ORDER BY type, name;
type   name                         tbl_name
-----  ---------------------------  --------
index  idx_visitors_country         visitors
index  sqlite_autoindex_visitors_1  visitors
table  visitors                     visitors
table  visits                       visits

sqlite> SELECT sql FROM sqlite_schema WHERE name = 'visits';
CREATE TABLE visits (
  id         INTEGER PRIMARY KEY,
  visitor_id INTEGER NOT NULL REFERENCES visitors(visitor_id) ON DELETE CASCADE,
  url        TEXT NOT NULL
)

The sqlite_autoindex_visitors_1 index is the one SQLite created for the UNIQUE constraint on email.

List every column of every table

Joining sqlite_schema with pragma_table_info() gives one row per column across the database:

sqlite> 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;
table_name  column_name    type
----------  -------------  -------
visitors    visitor_id     INTEGER
visitors    email          TEXT
visitors    country        TEXT
visitors    pages_visited  INTEGER
visitors    first_seen     TEXT
visits      id             INTEGER
visits      visitor_id     INTEGER
visits      url            TEXT

Show a table’s indexes and foreign keys

sqlite> PRAGMA index_list(visitors);
seq  name                         unique  origin  partial
---  ---------------------------  ------  ------  -------
0    idx_visitors_country         0       c       0
1    sqlite_autoindex_visitors_1  1       u       0
sqlite> PRAGMA index_info(idx_visitors_country);
seqno  cid  name
-----  ---  -------
0      2    country

sqlite> SELECT "table", "from", "to", on_delete FROM pragma_foreign_key_list('visits');
table     from        to          on_delete
--------  ----------  ----------  ---------
visitors  visitor_id  visitor_id  CASCADE

Get column names in Python

Run the pragma, or read cursor.description from any query. A LIMIT 0 query returns the names without fetching rows:

import sqlite3

con = sqlite3.connect("analytics.db")
# Option 1: ask SQLite for the table's columns
print([row[1] for row in con.execute("PRAGMA table_info(visitors)")])
# Option 2: read the column names of any query's result
cur = con.execute("SELECT * FROM visits LIMIT 0")
print([d[0] for d in cur.description])
con.close()

$ python3 columns.py
['visitor_id', 'email', 'country', 'pages_visited', 'first_seen']
['id', 'visitor_id', 'url']

Conclusion

Use .schema table in the shell when you want to read the definition. Use PRAGMA table_info or pragma_table_info() when code needs the columns, and table_xinfo when the table has generated columns. To list the tables themselves, see SQLite show tables.