SHOW TABLES in SQLite: listing tables and describing a schema

If you are sitting in the sqlite3 command-line shell:

.tables                  -- list every table
.schema                  -- show the CREATE statements for everything
.schema users            -- show the CREATE statement for one table
.databases               -- list attached databases

If you are writing SQL, from an application or a GUI, use these instead. They work anywhere:

-- list every table
SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name;

-- describe one table
PRAGMA table_info('users');

This is the single most common source of confusion, so it is worth being precise. Commands beginning with a dot are not SQL. They are features of the sqlite3 command-line program, which interprets them itself and never sends them to the database engine.

That is why this works in a terminal but fails the moment you put it in your code:

# In the sqlite3 shell: works
sqlite> .tables
orders  users

# In Python: fails
>>> cur.execute(".tables")
sqlite3.OperationalError: near ".": syntax error

Every SQLite database carries its own catalogue in a table called sqlite_master. Tables, views, indexes and triggers are all rows in it, so listing tables is an ordinary query:

SELECT name
FROM sqlite_master
WHERE type = 'table'
  AND name NOT LIKE 'sqlite_%'
ORDER BY name;

The NOT LIKE 'sqlite_%' filter matters. SQLite keeps internal objects such as sqlite_sequence, created by AUTOINCREMENT, in the same catalogue, and you almost never want those in a list you show to a person.

To include views as well, widen the filter to type IN ('table', 'view'). To get the original DDL for each object, select the sql column, which holds the exact CREATE statement as it was written.

Since SQLite 3.37.0 (2021-11-27) there is also PRAGMA table_list, which returns the schema, name, type, column count and whether the table is WITHOUT ROWID or STRICT. It is tidier than sqlite_master when it is available to you.

The closest thing to MySQL's DESCRIBE is PRAGMA table_info:

PRAGMA table_info('users');

It returns one row per column:

cid  name       type     notnull  dflt_value  pk
---  ---------  -------  -------  ----------  --
0    id         INTEGER  0                    1
1    name       TEXT     1                    0
2    email      TEXT     0                    0
3    created_at TEXT     0        CURRENT_TIMESTAMP  0

notnull is 1 when the column is NOT NULL. pk is 0 for ordinary columns, and for primary key columns it is the 1-based position within the key, which is how you read a composite primary key. dflt_value is the default expression as written.

If you would rather have this as a normal result set you can join against, SQLite exposes pragmas as table-valued functions too:

SELECT name, type, "notnull", pk
FROM pragma_table_info('users');

PRAGMA table_info tells you the shape of a table, but not how it was declared. For constraints, CHECK clauses and exact formatting, read the DDL SQLite stored when the table was created:

SELECT sql FROM sqlite_master WHERE name = 'users';

In the shell, .schema users prints the same thing. This is usually the fastest way to understand a table you did not write, because it shows intent, not just structure.

The rest of the catalogue is a pragma away:

PRAGMA index_list('users');          -- indexes on the table
PRAGMA index_info('idx_users_email'); -- columns in one index
PRAGMA foreign_key_list('orders');    -- foreign key constraints
PRAGMA database_list;                 -- attached databases

PRAGMA database_listis the honest answer to "how do I show all databases". SQLite has no server and therefore no global registry of databases: a database is just a file. What the pragma lists is what your current connection has open, which is main, temp, and anything you have ATTACHed.

The same queries work from any language, because they are ordinary SQL. In Python, where .tables fails, this is the equivalent:

import sqlite3

con = sqlite3.connect("app.db")
tables = [r[0] for r in con.execute(
    "SELECT name FROM sqlite_master "
    "WHERE type = 'table' AND name NOT LIKE 'sqlite_%' ORDER BY name"
)]

for t in tables:
    cols = con.execute(f"PRAGMA table_info('{t}')").fetchall()
    print(t, [c[1] for c in cols])

The same two statements are what any GUI runs behind the scenes when it draws you a table list, including our free browser-based SQLite viewer, which lists tables straight out of sqlite_master.

If you are arriving from MySQL or PostgreSQL: