How to show tables in SQLite

Quick Answer

SQLite has no SHOW TABLES. In the sqlite3 shell, use .tables:

sqlite3 shop.db
sqlite> .tables

From SQL (Python, Node, any app), query the schema table:

SELECT name FROM sqlite_schema
WHERE type = 'table' AND name NOT LIKE 'sqlite\_%' ESCAPE '\'
ORDER BY name;

sqlite_schema is the newer name for sqlite_master. Both work.

Show tables in the sqlite3 shell

.tables prints table and view names in columns. Give it a pattern to filter. It uses LIKE rules, so % is the wildcard and matching is case-insensitive:

sqlite> .tables
Order Items    customers      orders
big_orders     notes          notes_config ...

sqlite> .tables cust%
customers

sqlite> .tables 'Order%'
Order Items  orders

The last example shows the case-insensitivity: 'Order%' matched both Order Items and orders. Quote patterns that contain spaces.

List tables with SQL

Every SQLite database has a built-in table that holds one row per table, index, view and trigger:

SELECT type, name FROM sqlite_schema ORDER BY type, name;

On SQLite 3.37 or newer, PRAGMA table_list is more useful, because it labels each entry and covers every attached database:

SELECT schema, name, type
FROM pragma_table_list
WHERE type IN ('table', 'virtual')
  AND name NOT LIKE 'sqlite\_%' ESCAPE '\'
ORDER BY schema, name;

sqlite> SELECT schema, name, type FROM pragma_table_list ORDER BY 1, 2;
main|Order Items|table
main|big_orders|view
main|customers|table
main|notes|virtual
main|notes_config|shadow
...
other|archive|table
temp|scratch|table

From Python and Node.js

Python's built-in sqlite3 module. Use a raw string so the backslash reaches SQLite:

import sqlite3

con = sqlite3.connect("shop.db")
rows = con.execute(r"""
    SELECT name FROM sqlite_schema
    WHERE type = 'table' AND name NOT LIKE 'sqlite\_%' ESCAPE '\'
    ORDER BY name
""").fetchall()
print([r[0] for r in rows])

This still lists FTS shadow tables such as notes_data. Query pragma_table_list instead, as in the Node example, to skip them.

Node.js with better-sqlite3. .pluck() returns plain names instead of row objects:

import Database from 'better-sqlite3'

const db = new Database('shop.db', { readonly: true, fileMustExist: true })
const tables = db.prepare(`
  SELECT name FROM pragma_table_list
  WHERE schema = 'main' AND type = 'table'
    AND name NOT LIKE 'sqlite\\_%' ESCAPE '\\'
  ORDER BY name`).pluck().all()

console.log(tables) // [ 'Order Items', 'customers', 'orders', 'sqliteusers' ]

Why no tables show up

• You opened the wrong file, and SQLite created it. sqlite3 typo.db silently creates an empty 0-byte file, so .tables prints nothing. Check the path with .databases.
• The data is still in the WAL file. If the database uses WAL mode and you copied only the .db file, recent tables can be missing. Copy the -wal file too. See how to open a SQLite file.
• The table is in an attached database. Use pragma_table_list or .tables, not a plain sqlite_schema query.

Frequently Asked Questions

Is there a SHOW TABLES command in SQLite?

No. SHOW TABLES is MySQL syntax. Use .tables in the sqlite3 shell, or query sqlite_schema (also called sqlite_master) from SQL.

What is the difference between sqlite_master and sqlite_schema?

None. sqlite_schema is the name used since SQLite 3.33; sqlite_master still works as an alias.

How do I list only tables, not views?

Filter on type: WHERE type = 'table' in sqlite_schema, or type IN ('table', 'virtual') in pragma_table_list. The .tables command always includes views.

How do I count rows in every table?

SQLite keeps no row counts, so you have to run SELECT COUNT(*) on each table. In a script, loop over the table names and run a count for each.

Related Guides

• Open a database file

Free Tools

• SQL cheat sheet: joins, CTEs, window functions and dialect differences
• Schema designer: sketch tables or collections and their relationships in the browser