How can one see the structure of a table in SQLite

问题:

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?

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

回答2:

PRAGMA table_info(table_name);

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

回答3:

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

回答4:

.schema TableName

Where TableName is the name of the Table

回答5:

You should be able to see the schema by running

.schema <table>

回答6:

You will get the structure by typing the command:

.schema <tableName>

回答7:

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();
?>

回答8:

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

回答9:

I've implemented a SQLite table schema parser in PHP. You may check here: I implemented a sqlite table parser

https://github.com/maghead/sqlite-parser

The syntax follows the SQLite create table statement syntax: http://www.sqlite.org/lang_createtable.html