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

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 SQLite 3 command line tool?

Solution 1:

There are a few steps to see the tables in an SQLite database:

1. List the tables in your database:

   .tables
   

2. List how the table looks:

   .schema tablename
   

3. Print the entire table:

   SELECT * FROM tablename;
   

4. List all of the available SQLite prompt commands:

   .help
   

Solution 2:

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';

Solution 3:

It appears you need to go through the sqlite_master table, like this:

SELECT * FROM dbname.sqlite_master WHERE type='table';

And then manually go through each table with a SELECT or similar to look at the rows.

The .DUMP and .SCHEMA commands doesn't appear to see the database at all.

Solution 4:

To show all tables, use

SELECT name FROM sqlite_master WHERE type = "table"

To show all rows, I guess you can iterate through all tables and just do a SELECT * on each one. But maybe a DUMP is what you're after?