Advertisement
Advertisement


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 an SQLite database file - once I have attached it with the ATTACH command on the SQLite 3 command line tool?

2020/04/19
1
1215
4/19/2020 11:19:50 AM

Accepted Answer

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';
2008/09/17
589
9/17/2008 1:30:00 PM


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.

2014/06/12

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?

2008/09/17

Use .help to check for available commands.

.table

This command would show all tables under your current database.

2014/06/12

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
2017/09/01

To list the tables you can also do:

SELECT name FROM sqlite_master
WHERE type='table';
2013/03/13

Source: https://stackoverflow.com/questions/82875
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]