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

Question

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

Answer 1

The "helper" functions like .schema and .tables, don't look into ATTACHED databases. For the "main" database, they just query the SQLITE_MASTER table.

Want to learn SQL from basics! Here's the right video for you on SQL provided by Intellipaat:

Thus, if you used

ATTACH some_file.db1 AS my_db1;

then you need to do this:

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

Note: Temporary tables don't show up with .tables either.

Have a look at this video to understand the purpose of using TEMPORARY TABLE.

The temporary tables are used to keep temporary data. The benefit of using temporary tables is that they will be deleted when the current client session terminates.

 SELECT name FROM sqlite_temp_master WHERE type='table';