SQLite metadata refers to information about the database structure itself—the schema, tables, columns, indexes, views, triggers, and constraints that define how your data is organized. SQLite provides two main approaches for accessing metadata: PRAGMA commands and the sqlite_master system table.
1. Using PRAGMA Commands
PRAGMA is a special command in SQLite used to:
- Query and modify database configuration
- Retrieve metadata about database schema
- Control SQLite engine behavior
Unlike standard SQL commands, PRAGMA commands are SQLite-specific and provide direct access to database internals.
PRAGMA table_info()
One of the most commonly used PRAGMA commands for retrieving metadata is table_info(), which returns detailed information about the columns in a table.
PRAGMA table_info(table_name);
This will return a result set with the following columns:
-
cid: Column ID (the order of the column in the table) -
name: Column name -
type: Data type (TEXT, INTEGER, REAL, BLOB, etc.) -
notnull: 1 if the column has a NOT NULL constraint, 0 otherwise -
dflt_value: Default value for the column (or NULL if no default) -
pk: 1 if the column is part of the primary key, 0 otherwise
Example:
PRAGMA table_info(users);
Output:
cid | name | type | notnull | dflt_value | pk
----|---------|---------|---------|------------|----
0 | id | INTEGER | 0 | NULL | 1
1 | name | VARCHAR(120) | 1 | NULL | 0
2 | email | TEXT | 0 | NULL | 0
3 | role | VARCHAR(8) | 0 | 18 | 0
PRAGMA foreign_key_list()
This PRAGMA provides information about foreign key constraints defined on a table.
PRAGMA foreign_key_list(table_name);
Example:
PRAGMA foreign_key_list(posts);
PRAGMA index_list() and PRAGMA index_info()
These PRAGMAs allow you to inspect indexes. index_list shows all indexes on a table, and index_info provides details about a specific index.
-- List all indexes on a table
PRAGMA index_list(your_table_name);
-- Get details about a specific index
PRAGMA index_info(your_index_name);
Example:
-- First, get the list of indexes
PRAGMA index_list(users);
-- Output might show: idx_users_email
-- Then, get details about that index
PRAGMA index_info(idx_users_email);
Other Useful PRAGMA Commands
-- List all tables in the database
PRAGMA table_list;
-- Check database integrity
PRAGMA integrity_check;
-- Check foreign key constraints
PRAGMA foreign_key_check;
2. The sqlite_master Table
The sqlite_master table is SQLite's internal system catalog that stores metadata about all database objects (tables, indexes, views, and triggers). Unlike PRAGMA commands, you can query sqlite_master using standard SQL SELECT statements, making it more flexible for complex queries.
The sqlite_master table is read-only and maintained by SQLite itself.
Table Structure:
-
type: Type of object ('table', 'index', 'view', 'trigger') -
name: Name of the object -
tbl_name: Name of the table associated with the object (for indexes/views) -
rootpage: Root page number in the database file -
sql: SQL statement used to create the object
List All Tables
SELECT * FROM sqlite_master WHERE type='table';
Or to get just the table names:
SELECT name FROM sqlite_master
WHERE type='table'
ORDER BY name;
Get CREATE TABLE Statement
SELECT sql FROM sqlite_master
WHERE name='your_table_name'
AND type='table';
This returns the original CREATE TABLE statement, which is useful for understanding the complete table structure including constraints.
Example:
SELECT sql FROM sqlite_master WHERE name='users';
Output:
CREATE TABLE users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE,
age INTEGER DEFAULT 18
)
Find All Indexes for a Specific Table
SELECT name, sql
FROM sqlite_master
WHERE type='index'
AND tbl_name='your_table_name';
List All Views
SELECT name, sql
FROM sqlite_master
WHERE type='view';
List All Triggers
SELECT name, tbl_name, sql
FROM sqlite_master
WHERE type='trigger';
Get All Objects (Tables, Indexes, Views, Triggers)
SELECT type, name, tbl_name, sql
FROM sqlite_master
WHERE type IN ('table', 'index', 'view', 'trigger')
ORDER BY type, name;
Practical Example with Ruby
To demonstrate how these concepts work in practice, here's a Ruby class that wraps both PRAGMA commands and sqlite_master queries into a convenient API.
Top comments (1)
Great article! Thanks for sharing.