How can I list the tables in a SQLite database file that was opened with ATTACH?

When you ATTACH a secondary database in SQLite, you can query its schema much like the main database. The only difference is that each attached database has its own schema tables (e.g., sqlite_master or sqlite_schema in newer SQLite versions).

Basic Approach

Suppose you’ve attached a database with an alias called mydb:

ATTACH 'path/to/secondary.db' AS mydb;

To list tables in the attached database, simply query its schema:

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

If you’re using SQLite 3.33.0 or higher, you can also use sqlite_schema:

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

Explanation

Database Alias: When you run ATTACH 'file.db' AS mydb;, mydb is the alias used to reference this attached database.
Schema Table: Every SQLite database has a special table—sqlite_master (or sqlite_schema in recent SQLite)—that holds metadata about the database objects (tables, indexes, etc.).
Filtering by Type: By specifying WHERE type = 'table', you ensure that only tables (not indexes or views) are listed.

Example

-- Attach the secondary database
ATTACH 'C:/data/secondary.db' AS mydb;

-- List all tables from the attached database
SELECT name
FROM mydb.sqlite_master
WHERE type = 'table'
ORDER BY name;

This query will return a single column, name, containing the names of all tables in the mydb database.

Recommended Courses

CONTRIBUTOR

TechGrind