298

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

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Ali
  • 10,774
  • 10
  • 56
  • 83

8 Answers8

423
PRAGMA table_info(table_name);

This will work for both: command-line and when executed against a connected database.

A link for more details and example. thanks SQLite Pragma Command

MindRoasterMir
  • 324
  • 1
  • 2
  • 18
AnonGeek
  • 7,408
  • 11
  • 39
  • 55
  • 9
    execute `.header on` before execute above command. you can view columns with data. – Mangala Edirisinghe Oct 30 '13 at 07:27
  • 2
    This should be the accepted answer. .tables and .schema display only "the original CREATE TABLE and CREATE INDEX statements". Any subsequent changes will NOT be reflected. – Blago Jan 30 '15 at 09:51
  • 2
    If you want to get the table info for an attached database 'foo', you run `PRAGMA foo.table_info(table_name);` – paddy May 01 '17 at 05:49
  • This will also show the underlying structure of virtual tables. (http://sqlite.org/vtab.html) – Alcamtar May 23 '18 at 20:30
379

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

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
metamatt
  • 13,809
  • 7
  • 46
  • 56
  • 24
    one common mistake here is confusing these utility functions with sql statements.. the former doesn't require a `;` at the end of the command where as the latter does. – abbood Jun 26 '13 at 05:12
  • 2
    Doesn't show me the index for me even though I have a primary key on two columns together – pratnala Mar 13 '14 at 18:08
  • doesn't work for me but the answer by AnonGeek does – kkurian Jan 24 '15 at 22:07
  • 7
    .tables and .schema display only "the original CREATE TABLE and CREATE INDEX statements". Any subsequent changes will NOT be reflected. You should really use PRAGMA table_info(table_name) instead. – Blago Jan 30 '15 at 09:50
  • @Blago: Even though EXPLAIN QUERY PLAN shows an autoindex on my table, PRAGMA table_info(table_name) doesn't show the autoindex. However I think that must be because sqlite "created an automatic index that lasts only for the duration of a single SQL statement". (https://www.sqlite.org/optoverview.html#automatic_indexes) – LarsH Aug 22 '18 at 03:11
  • `.schema users;` doesn't show anything for me. the table exists though. – chovy Feb 17 '22 at 21:20
  • 1
    @chovy try `.schema users` instead of `.schema users;` (everyone in general that this is not working for might want to try this too) – ijoseph Jun 05 '22 at 04:31
  • Weird. It worked. Thought I was going crazy. – chovy Jun 05 '22 at 12:55
48

You can query sqlite_schema (which was historically called sqlite_master).

SELECT sql FROM sqlite_schema 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_schema 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
Micha Wiedenmann
  • 19,979
  • 21
  • 92
  • 137
  • Just to mention that it seems to also work the SQL: SELECT sql FROM sqlite_master WHERE tbl_name='foo'; – RDP Jan 10 '17 at 02:06
18

You should be able to see the schema by running

.schema <table>
Costa Walcott
  • 867
  • 6
  • 16
14

.schema TableName

Where TableName is the name of the Table

Abi
  • 4,718
  • 4
  • 20
  • 29
11

You will get the structure by typing the command:

.schema <tableName>
aTJ
  • 3,823
  • 3
  • 21
  • 25
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();
?>
Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Gil
  • 39
  • 2
1

You can use the Firefox add-on called SQLite Manager to view the database's structure clearly.

UkFLSUI
  • 5,509
  • 6
  • 32
  • 47
Jack
  • 29
  • 1