SQLite FAQ - How to show the schema for a SQLite database table

SQLite schema FAQ: How do I list the schema for SQLite database table?

Solution

To view the schema for a SQLite database table, just use the SQLite schema command, like this:

sqlite> .schema salespeople

Note that, as shown, you need a . before the schema command, and don't need to use a semi-colon at the end of that command.

For an example SQLite salespeople table I created, this schema command produces the following output:

CREATE TABLE salespeople (
    id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    commission_rate REAL NOT NULL
);

How to show the SQLite schema for multiple tables

You can display the schema for multiple SQLite database tables at one time by adding a LIKE clause to the SQLite schema command, as shown here:

sqlite> .schema '%ers'

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    company_name TEXT NOT NULL,
    street_address TEXT NOT NULL,
    city TEXT NOT NULL,
    state TEXT NOT NULL,
    zip TEXT NOT NULL
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    salesperson_id INTEGER,
    FOREIGN KEY(customer_id) REFERENCES customers(id),
    FOREIGN KEY(salesperson_id) REFERENCES salespeople(id)
);

That command displays the CREATE TABLE schema for all SQLite database tables whose name ends with the string “ers”.