SQLite Show Tables: How to List All Tables in a Database

Ninad Pathak

October 7, 2026

SQLite doesn’t have a SHOW TABLES command; typing it gives a syntax error. Every database file does keep a list of its tables, and in this article I’ll walk you through every way to read it, from the sqlite3 shell and from code.

TLDR

In the sqlite3 shell, type .tables.
In SQL or in your own code, query the built-in sqlite_schema table. The full query is in Step 3.

On SQLite 3.37.0 or later, PRAGMA table_list; also lists tables, with extra details.

To list databases instead of tables, use .databases or PRAGMA database_list;.

What does “show tables” mean in SQLite?

Showing tables means getting the list of table names stored in a database file. SQLite keeps that list in a built-in table called sqlite_schema, and the sqlite3 command-line shell reads it for you when you type .tables. Every table and index in the database has a row there, and so does every view and trigger.

SHOW TABLES is MySQL syntax, so SQLite rejects it:

$ sqlite3 blog.db "SHOW TABLES;"
Error: in prepare, near "SHOW": syntax error
  SHOW TABLES;
  ^--- error here

Older code and older answers call the table sqlite_master. Both names work. The name sqlite_schema arrived in SQLite 3.33.0 (2020).

SQLite show tables syntax

SQLite gives you three ways to list tables. Pick the one that matches where you are:

Where you are	What to run
The sqlite3 shell	`.tables ?TABLE?`
Any SQL client or program	`SELECT name FROM sqlite_schema WHERE type = 'table' ...`
Any SQL client (SQLite 3.37.0+), when you want details	`PRAGMA table_list;`

The ?TABLE? part is optional. It’s a LIKE pattern, so % matches any run of characters.

How to list tables in an SQLite database

To show you how this works, I will use a small blog database. It has four tables. It also has a view and an index.

Step 1: Create the example database

Save this as schema.sql:

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);
CREATE TABLE posts (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL REFERENCES users(id),
  title TEXT NOT NULL,
  published_at TEXT
);
CREATE TABLE comments (
  id INTEGER PRIMARY KEY,
  post_id INTEGER NOT NULL REFERENCES posts(id),
  body TEXT NOT NULL
);
CREATE TABLE post_tags (
  post_id INTEGER NOT NULL,
  tag TEXT NOT NULL
);
CREATE INDEX idx_posts_user ON posts(user_id);
CREATE VIEW recent_posts AS
  SELECT id, title FROM posts WHERE published_at >= '2026-01-01';
INSERT INTO users (name) VALUES ('Ada');
INSERT INTO posts (user_id, title, published_at) VALUES (1, 'Hello', '2026-02-01');

Then create the database from it with sqlite3 blog.db < schema.sql.

Step 2: List tables in the sqlite3 shell

Run .tables:

$ sqlite3 blog.db ".tables"
comments      post_tags     posts         recent_posts  users

You get the views as well: recent_posts is a view. SQLite’s internal tables stay hidden, such as the sqlite_sequence table that SQLite creates for any table using AUTOINCREMENT.

Add a pattern to narrow the list:

$ sqlite3 blog.db ".tables post%"
post_tags  posts

Step 3: List tables with SQL

Outside the shell there’s no .tables, so you query sqlite_schema. I saved this query as list_tables.sql:

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

Then run it:

$ sqlite3 blog.db < list_tables.sql
comments
post_tags
posts
users

This works from any SQLite client or language driver. The view is gone here, because the query asks for type = 'table' only.

Step 4: Get more detail with PRAGMA table_list

PRAGMA table_list returns one row per table or view, with its schema and its column count:

$ sqlite3 -header -column blog.db "PRAGMA table_list;"
schema  name                type   ncol  wr  strict
------  ------------------  -----  ----  --  ------
main    post_tags           table  2     0   0
main    comments            table  3     0   0
main    sqlite_schema       table  5     0   0
main    users               table  2     0   0
main    recent_posts        view   2     0   0
main    posts               table  4     0   0
main    sqlite_sequence     table  2     0   0
temp    sqlite_temp_schema  table  5     0   0

It includes the internal tables and covers every attached schema. The wr column marks WITHOUT ROWID tables, and strict marks STRICT tables. Rows come back in no particular order. In a query, the same data is in pragma_table_list, for example SELECT schema, name, type, ncol FROM pragma_table_list;.

Examples

Each example below runs against the same blog database.

See everything in the database

Drop the WHERE clause to see every object, not only tables:

$ sqlite3 -header -column blog.db \
  "SELECT type, name, tbl_name FROM sqlite_schema ORDER BY type, name;"
type   name             tbl_name
-----  ---------------  ---------------
index  idx_posts_user   posts
table  comments         comments
table  post_tags        post_tags
table  posts            posts
table  sqlite_sequence  sqlite_sequence
table  users            users
view   recent_posts     recent_posts

Change the type filter to list views instead:

$ sqlite3 blog.db "SELECT name FROM sqlite_schema WHERE type = 'view';"
recent_posts

List tables in an attached database or temp tables

After ATTACH, .tables shows the attached tables with their schema name in front. In SQL, query that schema’s own sqlite_schema:

sqlite> ATTACH 'archive.db' AS archive;
sqlite> .tables
archive.old_posts  post_tags          recent_posts
comments           posts              users
sqlite> SELECT name FROM archive.sqlite_schema WHERE type = 'table';
old_posts

Temporary tables get a temp. prefix. In SQL, they’re listed in temp.sqlite_schema:

sqlite> CREATE TEMP TABLE scratch (x);
sqlite> .tables
comments      posts         temp.scratch
post_tags     recent_posts  users

Show databases with .databases and PRAGMA database_list

SQLite has no SHOW DATABASES either. A single connection can have several database files open at once: the main one, plus any you ATTACH. The shell’s .databases command lists their names and files, and PRAGMA database_list returns the same thing as rows:

sqlite> ATTACH 'archive.db' AS archive;
sqlite> .databases
main: /private/tmp/sqldocs-demo/blog.db r/w
archive: /private/tmp/sqldocs-demo/archive.db r/w
sqlite> .mode column
sqlite> .headers on
sqlite> PRAGMA database_list;
seq  name     file
---  -------  ------------------------------------
0    main     /private/tmp/sqldocs-demo/blog.db
2    archive  /private/tmp/sqldocs-demo/archive.db

r/w means the file is open for reading and writing. The paths are from my Mac, so yours will differ. The seq column skips 1, because that slot belongs to temp, the database that holds TEMP tables. It appears once you create one:

sqlite> ATTACH 'archive.db' AS archive;
sqlite> CREATE TEMP TABLE scratch (x);
sqlite> .mode column
sqlite> .headers on
sqlite> PRAGMA database_list;
seq  name     file
---  -------  ------------------------------------
0    main     /private/tmp/sqldocs-demo/blog.db
1    temp
2    archive  /private/tmp/sqldocs-demo/archive.db

List tables from Python

Python’s built-in sqlite3 module runs the same query:

import sqlite3

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

$ python3 list_tables.py
['comments', 'post_tags', 'posts', 'users']

The r before the string keeps Python from treating the backslashes as escapes. If your Python is linked to an SQLite older than 3.33.0, check sqlite3.sqlite_version and use sqlite_master instead. In Node.js the query is the same. SQLite in Node.js shows how to connect with the built-in node:sqlite module.

Edge cases

The filter in the Step 3 query looks odd, with its backslash and ESCAPE '\'. I wrote it that way on purpose, because the plain version hides tables it shouldn’t.

Why the query needs ESCAPE

The common filter NOT LIKE 'sqlite_%' has a bug. In LIKE, _ matches any one character. So the filter also hides a table of your own called sqliteusers:

$ sqlite3 u.db "CREATE TABLE sqliteusers (id INTEGER PRIMARY KEY);"
$ sqlite3 u.db "SELECT name FROM sqlite_schema WHERE type = 'table' AND name NOT LIKE 'sqlite_%';"
$ sqlite3 u.db < list_tables.sql
sqliteusers

The unescaped query prints nothing. The escaped one finds the table, because ESCAPE '\' makes \_ a literal underscore. Names that really start with sqlite_ are always internal, since SQLite won’t let you create one:

$ sqlite3 u.db "CREATE TABLE sqlite_users (id INTEGER PRIMARY KEY);"
Error: in prepare, object name reserved for internal use: sqlite_users

.tables hides the same tables

The shell’s .tables has the same blind spot, even when you give it a pattern:

$ sqlite3 u.db "CREATE TABLE notes (id INTEGER PRIMARY KEY);"
$ sqlite3 u.db ".tables"
notes
$ sqlite3 u.db ".tables sqlite%"
$ sqlite3 u.db < list_tables.sql
notes
sqliteusers

If a table could have a name like that, use the escaped query or PRAGMA table_list instead of .tables.

Conclusion

SQLite has no SHOW TABLES, but it gives you three ways to get the same list. In the shell, .tables is the quickest. In code, query sqlite_schema with the escaped filter, so tables like sqliteusers don’t disappear. When you also want column counts or the STRICT flag, use PRAGMA table_list. To see a table’s columns, SQLite’s answer to DESCRIBE, use PRAGMA table_info as shown in how to describe a table in SQLite.