SQLite | Table | Inspecting a Table Schema

Tags:

• SQLite
• Database

This article explains how to inspect the CREATE statement used to create a table. You can query the sqlite_master table or use the .schema command.

Querying the sqlite_master Table

The first method uses the sqlite_master table. Run the following SQL statement. For easier reading, the example first changes the output mode to line with .mode.

select * from sqlite_master;

sqlite> select * from sqlite_master;
table|customer|customer|2|CREATE TABLE customer (id, name)
table|customer2|customer2|3|CREATE TABLE customer2 (id integer, name text)
sqlite> 
sqlite> .mode line
sqlite> 
sqlite> select * from sqlite_master;
    type = table
    name = customer
tbl_name = customer
rootpage = 2
     sql = CREATE TABLE customer (id, name)

    type = table
    name = customer2
tbl_name = customer2
rootpage = 3
     sql = CREATE TABLE customer2 (id integer, name text)
sqlite> 

The data can be queried as shown above.

Because two tables currently exist, the sqlite_master table returns two rows. In each row, the name column contains the table name and the sql column contains the CREATE TABLE statement used to create it. Querying sqlite_master therefore reveals how each table was created.

To inspect the CREATE TABLE statement for a particular table, add a WHERE clause.

select * from sqlite_master where type='table' and name='customer';

sqlite> select * from sqlite_master where type='table' and name='customer';
    type = table
    name = customer
tbl_name = customer
rootpage = 2
     sql = CREATE TABLE customer (id, name)

Only the row for the specified table is returned.

Using the .schema Command

To inspect only schema information, you can also use SQLite’s .schema command.

.schema
.schema ?TABLE?

With no argument, the command displays schema information for all tables and indexes. When a table name is supplied, it displays schema information for the matching table and related objects.

Run .schema as follows.

.schema

sqlite> .schema
CREATE TABLE customer (id, name);
CREATE TABLE customer2 (id integer, name text);
sqlite> 

The output contains the CREATE statements for the two tables in the current database. The .schema command also shows how the tables were created.