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

database-schema, sql, sqlite

Solution

The `.tables`, and `.schema` "helper" functions don't look into ATTACHed databases: they just query the `SQLITE_MASTER` table for the "main" database. Consequently, if you used

ATTACH some_file.db AS my_db;

then you need to do

SELECT name FROM my_db.sqlite_master WHERE type='table';

Note that temporary tables don't show up with `.tables` either: you have to list `sqlite_temp_master` for that:

SELECT name FROM sqlite_temp_master WHERE type='table';

Problem

What SQL can be used to list the tables, and the rows within those tables in an SQLite database file – once I have attached it with the `ATTACH` command on the `sqlite3` command line tool?

Original source