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

A Walkthrough SQLite's Attached Databases

Table Listing in Attached Databases

Although the .tables command conveniently lists tables for the main database, it doesn't cater to attached databases. But, applying the command SELECT name FROM {alias}.sqlite_master WHERE type='table'; with the correct alias can retrieve your needed table list for the attached database.

Interrogating Attached Database Schema

For this purpose, .schema tablename won't work for attached databases. A slight twist in your command will get you there:

ATTACH 'example.db' AS exdb;
SELECT sql FROM exdb.sqlite_master WHERE type='table' AND name='your_table';


Just like hacking, but legal. This command fetches you the SQL statement used to create the table in the attached database.

Data Querying in Attached Databases

Fetch the data from a table within an attached database by replicating the SELECT statement, ensuring to prefix the table name with the database's alias:

SELECT * FROM exdb.your_table;


Way to go, you just aced the Data Heist!

Temporary Tables: Invisible Yet There

Temporary tables won't surrender their presence using the .tables command. Resort to sqlite_temp_master to hunt down temporary tables with another variety of SELECT command:

SELECT name FROM sqlite_temp_master WHERE type='table'; //Like saying, "Observe Commander, where hidden tables lie!" 


The Master Check: All Table Data and Names

To get the grand view of encompassing all tables and their names within the complete database environment (including attached ones), loop through database aliases and collect table vita from each sqlite_master:

PRAGMA database_list;
-- Replace 'db_alias' with the actual alias from the database list for every twist and turn.
SELECT name FROM db_alias.sqlite_master WHERE type='table'; //SQL-genie at your service!


The '.dump' Command and its Limitations

It's time to note, the .dump command can print complete schema and data snapshots of the main database. But attached databases won't entertain .dump. You will have to manually script or export data from attached databases.

The Saviour: SQLite prompt help

The .help command at the SQLite prompt is your friendly companion to offer command help and usage information for SQLite-related commands, ensuring you never get stuck.