How can one see the structure of a table in SQLite

Question

This question already has an answer here:

• Is there an SQLite equivalent to MySQL's DESCRIBE [table]? 5 answers

How can I see the structure of table in SQLite as desc was in Oracle?

Answer 1

Invoke the sqlite3 utility on the database file, and use its special dot commands:

• .tables will list tables
• .schema [tablename] will show the CREATE statement(s) for a table or tables

There are many other useful builtin dot commands -- see the documentation at http://www.sqlite.org/sqlite.html, section Special commands to sqlite3.

Example:

sqlite> entropy:~/Library/Mail>sqlite3 Envelope\ Index
SQLite version 3.6.12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
addresses              ews_folders            subjects
alarms                 feeds                  threads
associations           mailboxes              todo_notes
attachments            messages               todos
calendars              properties             todos_deleted_log
events                 recipients             todos_server_snapshot
sqlite> .schema alarms
CREATE TABLE alarms (ROWID INTEGER PRIMARY KEY AUTOINCREMENT, alarm_id,
                     todo INTEGER, flags INTEGER, offset_days INTEGER,
                     reminder_date INTEGER, time INTEGER, argument,
                     unrecognized_data BLOB);
CREATE INDEX alarm_id_index ON alarms(alarm_id);
CREATE INDEX alarm_todo_index ON alarms(todo);

Note also that SQLite saves the schema and all information about tables in the database itself, in a magic table named sqlite_master, and it's also possible to execute normal SQL queries against that table. For example, the documentation link above shows how to derive the behavior of the .schema and .tables commands, using normal SQL commands (see section: Querying the database schema).

Answer 2

You can query sqlite_master

SELECT sql FROM sqlite_master WHERE name='foo';

which will return a create table SQL statement, for example:

$ sqlite3 mydb.sqlite
sqlite> create table foo (id int primary key, name varchar(10));
sqlite> select sql from sqlite_master where name='foo';
CREATE TABLE foo (id int primary key, name varchar(10))

sqlite> .schema foo
CREATE TABLE foo (id int primary key, name varchar(10));

sqlite> pragma table_info(foo)
0|id|int|0||1
1|name|varchar(10)|0||0

Answer 3

If you are using PHP you can get it this way:

<?php
    $dbname = 'base.db';
    $db = new SQLite3($dbname);
    $sturturequery = $db->query("SELECT sql FROM sqlite_master WHERE name='foo'");

    $table = $sturturequery->fetchArray();
    echo '<pre>' . $table['sql'] . '</pre>';

    $db->close();
?>