How do I list the tables in a SQLite database file

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?

From stackoverflow

• 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.

  izb : awesome, thanks.

• .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..

  FilmJ : FYI: for a list of all the commands understood, try ".help" at your sqlite3 prompt.

• To list the tables you can also do:

  SELECT name FROM sqlite_master WHERE type='table';

• 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);
  

• 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?

• 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
  

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

• 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

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

• select * from table table_name;