How to list the tables in a SQLite database file t

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

Try PRAGMA table_info(table-name);
http://www.sqlite.org/pragma.html#schema

Answer 2

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?

Answer 3

Use .help to check for available commands.

.table

This command would show all tables under your current database.

Answer 4

The easiest way to do this is to open the database directly and use the .dump command, rather than attaching it after invoking the SQLite 3 shell tool.

So... (assume your OS command line prompt is $) instead of $sqlite3:

sqlite3> ATTACH database.sqlite as "attached"

From your OS command line, open the database directly:

$sqlite3 database.sqlite
sqlite3> .dump

Answer 5

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

Answer 6

To list the tables you can also do:

SELECT name FROM sqlite_master
WHERE type='table';