sqlite3 explain table_name

Question

In mysql you can view a table's structure via explain tablename; What is the equivalent for sqlite3?

Similar question http://stackoverflow.com/questions/305378/get-list-of-tables-db-schema-dump-etc-in-python-sqlite3 — unode, Nov 11 '10 at 16:32

score 2 · Answer 1 · answered Nov 11 '10 at 16:30

You can use .schema in the Command Line Shell:

With no arguments, the ".schema" command shows the original CREATE TABLE and CREATE INDEX statements that were used to build the current database. If you give the name of a table to ".schema", it shows the original CREATE statement used to make that table and all if its indices.

score 2 · Accepted Answer · answered Nov 11 '10 at 16:31

2

I believe ".schema tablename" is what you're looking for.

answered Nov 11 '10 at 16:31

score 2 · Answer 3 · edited May 23 '17 at 12:29

This was already answered in a more generic way here.

Edit:

Note that .schema will also give you INDEXES that match the same name.

Example:

CREATE TABLE job (
    id INTEGER PRIMARY KEY,
    data VARCHAR
);
CREATE TABLE job_name (
    id INTEGER PRIMARY KEY,
    name VARCHAR
);
CREATE INDEX job_idx on job(data);

Note the differences between:

sqlite> SELECT sql FROM SQLITE_MASTER WHERE type = 'table' AND name = 'job';
CREATE TABLE job (
        id INTEGER PRIMARY KEY,
        data VARCHAR
    )
sqlite> SELECT sql FROM SQLITE_MASTER WHERE name = 'job_idx';
CREATE INDEX job_idx on job(data)

and

sqlite> .schema job
CREATE TABLE job (
        id INTEGER PRIMARY KEY,
        data VARCHAR
    );
CREATE INDEX job_idx on job(data);

Including the semi-colon at the end of the queries.

It was indeed. My question is a little more specific though. — tybro0103, Nov 11 '10 at 16:42

sqlite3 explain table_name

3 Answers3