How to 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?
.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';
There are a few steps to see the tables in an SQLite database:
List the tables in your database:
List how the table looks:
Print the entire table:
SELECT * FROM tablename;
List all of the available SQLite prompt commands:
Read more... Read less...
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.
.SCHEMA commands doesn't appear to see the database at all.
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?
.help to check for available commands.
This command would show all tables under your current database.
There is a command available for this on the SQLite command line:
.tables ?PATTERN? List names of tables matching a LIKE pattern
Which converts to the following SQL:
SELECT name FROM sqlite_master WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%' UNION ALL SELECT name FROM sqlite_temp_master WHERE type IN ('table','view') ORDER BY 1
To list the tables you can also do:
SELECT name FROM sqlite_master WHERE type='table';