Sometimes you just want to see the basic structure of a table.

In SQLite, there are several ways to retrieve information about a given table. In particular, here are four options:

• The PRAGMA table_info() statement
• The PRAGMA table_xinfo() statement (for virtual tables)
• The .schema command
• The sqlite_master table

Examples of each method are below.

PRAGMA table_info()

The PRAGMA table_info() statement returns one row for each column in the named table. It’s kind of like an SQLite equivalent to the DESCRIBE statement in MySQL.

Syntax

The syntax goes like this:

PRAGMA schema.table_info(table-name);

Where table-name is the name of the table you need information about.

The schema part is optional. This is the name of an attached database or main or temp for the main and the TEMP databases. If you don’t provide this then the main database will be used.

Example

Here’s an example of returning information about a table called Pets.

PRAGMA table_info(Pets);

Result:

cid         name        type        notnull     dflt_value  pk        
----------  ----------  ----------  ----------  ----------  ----------
0           PetId       INTEGER     0                       1         
1           PetName                 0                       0         
2           TypeId                  0                       0         

In this case, only the first column (PetId) has its data type explicitly defined. The others were created without their types being explicitly defined.

We can also see that the PetId column is the primary key column (there’s a 1 in the pk column).

PRAGMA table_xinfo()

The PRAGMA table_xinfo() statement is exactly the same as the PRAGMA table_info() statement, except that it also returns hidden columns on virtual tables.

If you want to see the structure of a virtual table, including any hidden columns, this is the one to use.

Here’s an example using the same table from the previous example.

PRAGMA table_xinfo(Pets);

Result:

cid         name        type        notnull     dflt_value  pk          hidden    
----------  ----------  ----------  ----------  ----------  ----------  ----------
0           PetId       INTEGER     0                       1           0         
1           PetName                 0                       0           0         
2           TypeId                  0                       0           0         

You might have to scroll sideways to see the extra column. In this case none of the columns are hidden (and it’s not a virtual table), so all rows are 0 for that column.

Here’s the result again, but using vertical output (.mode line) so that you don’t have to scroll sideways.

.mode line
PRAGMA table_xinfo(Pets);

Result:

       cid = 0
      name = PetId
      type = INTEGER
   notnull = 0
dflt_value = 
        pk = 1
    hidden = 0


       cid = 1
      name = PetName
      type = 
   notnull = 0
dflt_value = 
        pk = 0
    hidden = 0


       cid = 2
      name = TypeId
      type = 
   notnull = 0
dflt_value = 
        pk = 0
    hidden = 0

The .schema Command

Another way of retrieving a table’s structure is by using the .schema command. This is one of several methods you can use to return the SQL used to create the table.

Here’s an example using the same table from the previous example.

.schema Pets

Result:

CREATE TABLE Pets( 
    PetId INTEGER PRIMARY KEY, 
    PetName,
    TypeId,
    FOREIGN KEY(TypeId) REFERENCES Types(TypeId)
);

This method allows us to see more information about the table. In this example, we can see that the TypeId column is actually a foreign key that references a column in another table.

The sqlite_master Table

You can use the sqlite_master table to the same thing as the previous example.

Here’s an example using the same table.

SELECT sql 
FROM sqlite_master 
WHERE tbl_name = 'Pets';

Result:

CREATE TABLE Pets( 
    PetId INTEGER PRIMARY KEY, 
    PetName,
    TypeId,
    FOREIGN KEY(TypeId) REFERENCES Types(TypeId)
)