SQLite is a popular open-source database that is easy to use and ideal for small, embedded database applications. When working with SQLite, it’s important to understand how to get metadata about your database tables, such as column names and data types.
This allows you to inspect the structure of your tables and ensure your data is stored as intended. In SQLite, the
PRAGMA table_info() statements are used to describe tables.
Also read: SQLite Show Tables
What's in this article
A Simple Example
Let’s look at a basic example. First, we’ll create a table called
users with two columns:
CREATE TABLE users ( id INTEGER PRIMARY KEY, name TEXT );
To describe this table, we can run:
id INTEGER 0 1 name TEXT 0 0
This shows us the column names, data types, whether they can be NULL, and the default values.
Using PRAGMA table_info()
PRAGMA table_info() statement provides more detailed metadata about a table. Running:
The columns provided are:
- Column index
- Column name
- Data type
- Whether the column can be NULL
- Default value
- Whether the column is auto-incremented
This provides additional useful details compared to the simple
Another example of using SQLite DESCRIBE
Let’s look at one more example with some sample analytics data-related columns. First we’ll create a
CREATE TABLE pageviews ( id INTEGER PRIMARY KEY, page_url TEXT, visitor_id INTEGER, visited_at DATETIME )
This contains some common web analytics columns. To inspect it, we can run:
Which outputs details on each column:
0|id|INTEGER|1||1 1|page_url|TEXT|0||0 2|visitor_id|INTEGER|0||0 3|visited_
This helps confirm that the table matches our intended structure before adding any data.
PRAGMA table_info() statements are invaluable for inspecting and validating table schemas in SQLite. By mastering their use, you can be confident you are storing data as expected.