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

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

As of the latest versions of SQLite 3 you can issue:

.fullschema

to see all of your create statements.

Answer 3

I use this query to get it:

SELECT name FROM sqlite_master WHERE type='table'

And to use in iOS:

NSString *aStrQuery=[NSString stringWithFormat:@"SELECT name FROM sqlite_master WHERE type='table'"];

Answer 4

Via a union all, combine all tables into one list.

select name
from sqlite_master 
where type='table'

union all 

select name 
from sqlite_temp_master 
where type='table'

Answer 5

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 6

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