SQLite list tables

There are two ways to list tables in SQLite: the .tables dot command in the CLI, or a SELECT against sqlite_master when working in SQL.

Quick answer

In the SQLite CLI:

.tables

In SQL (works anywhere):

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

Using .tables in the SQLite CLI

The .tables command lists all user tables in the current database:

sqlite> .tables
orders   products   users

You can filter by name using a pattern with % as a wildcard:

sqlite> .tables user%
users   user_roles

.tables does not show SQLite's internal system tables. It also does not show views unless you use a different command.

Querying sqlite_master

sqlite_master is a system table that holds the definition of every object in the database. Each row represents one object: a table, index, view, or trigger.

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

name
--------
orders
products
users

This query works in the CLI, in application code, and in any SQLite GUI. It is the portable option when .tables is not available.

sqlite_schema

SQLite 3.33.0 (released 2020) added sqlite_schema as the official name for sqlite_master. Both names refer to the same table, and both work in current SQLite versions.

SELECT name FROM sqlite_schema WHERE type='table' ORDER BY name;

Use sqlite_master if you need to support older SQLite versions.

Including views

To list tables and views together, expand the type filter:

SELECT name, type
FROM sqlite_master
WHERE type IN ('table', 'view')
ORDER BY name;

Checking if a table exists

To check whether a specific table exists, filter by name:

SELECT name FROM sqlite_master WHERE type='table' AND name='users';

The query returns one row if the table exists, or no rows if it does not. This is useful in migration scripts before running CREATE TABLE or ALTER TABLE.

Getting a table's schema

To see the SQL used to create a table, use .schema in the CLI:

sqlite> .schema users
CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE
);

Or query sqlite_master directly:

SELECT sql FROM sqlite_master WHERE name='users';

Both return the original CREATE TABLE statement.

Listing tables in Python

With the sqlite3 module, query sqlite_master through a cursor:

import sqlite3

conn = sqlite3.connect("store.db")
cursor = conn.cursor()

cursor.execute("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name")
tables = cursor.fetchall()

for (name,) in tables:
    print(name)

Temporary tables

Temporary tables are not stored in sqlite_master. They are stored in sqlite_temp_master instead:

SELECT name FROM sqlite_temp_master WHERE type='table';

Temporary tables exist only for the duration of the database connection and are not visible to other connections.

Quick reference

| Goal | Command | |------|---------| | List all tables (CLI) | .tables | | List tables matching a pattern (CLI) | .tables pattern% | | List all tables (SQL) | SELECT name FROM sqlite_master WHERE type='table' | | List tables and views | SELECT name FROM sqlite_master WHERE type IN ('table', 'view') | | Check if a table exists | SELECT name FROM sqlite_master WHERE type='table' AND name='tablename' | | Show table schema (CLI) | .schema tablename | | Show table schema (SQL) | SELECT sql FROM sqlite_master WHERE name='tablename' | | List temporary tables | SELECT name FROM sqlite_temp_master WHERE type='table' |