How do I list the tables in a SQLite database file

Question

What SQL can be used to list the tables, and the rows within those tables, in a SQLite database file once i've ATTACHed it on the sqlite3 command line tool?

Answer 1

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.

Answer 2

.tables

Will list the tables in your database

.schema tablename

will list how the table looks

and a

Select * from tablename

will print the entire table..

Answer 3

To list the tables you can also do:

SELECT name FROM sqlite_master WHERE type='table';

Answer 4

The ".schema" commando will list available tables and their rows, by showing you the statement used to create said tables:

sqlite> create table_a (id int, a int, b int);
sqlite> .schema table_a
CREATE TABLE table_a (id int, a int, b int);

Answer 5

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 6

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

Answer 7

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 8

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

So... (assume your o/s command line prompt is $) instead of

$sqlite3

sqlite3> ATTACH database.sqlite as "attached"

from your o/s command line, open the database directly

$sqlite3 database.sqlite

sqlite3> .dump

Answer 9

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

Answer 10

select * from table table_name;