PRAGMA statements for metadata

PRAGMA statements are special SQLite-specific SQL extensions that allow you to interact with the SQLite engine and retrieve internal, non-table data.

SQLite offers a set of PRAGMA statements that return metadata about the database structure and rules, including details on tables, columns, constraints, relationships, indexes, and views.

The following table lists PRAGMA statements supported by Devart ODBC Driver for SQLite.

PRAGMA	Description
PRAGMA database_list;	Returns one row for each database attached to the current connection. The second column in the output shows the database name (main – your primary database, temp – temporary database for TEMP objects, or the name of an attached database used in the ATTACH statement). The third column shows the database file name, or an empty string if the database is not associated with a file.
PRAGMA [schema.]table_list[(table-name)];	Returns one row for each table or view. By default, lists objects in all schemas. To limit results to a specific schema, add a schema. prefix. To return information only for a specific table, specify it as (table-name). The output includes: schema – schema name, name – object name, type – object type (table, view, shadow, or virtual), ncol – column count (including generated and hidden columns), the wr flag (1 if the table is WITHOUT ROWID, 0 if not), and the strict flag (1 if the table is STRICT, 0 if not).
PRAGMA schema.table_info(table-name);	Returns one row for each normal (non-generated and non-hidden) column in the specified table or view. The output includes: cid – rank in the result set, name – column name, type – declared type (or an empty string), notnull – flag indicating whether the column can be NULL, dflt_value – default value, and pk – position in the primary key (0 if not in the primary key, otherwise the 1-based index). Use PRAGMA table_xinfo to include generated and hidden columns.
PRAGMA schema.table_xinfo(table-name);	Returns one row for each column in the specified table, including generated and hidden columns. The output matches PRAGMA table_info and adds the hidden flag (0 – normal column, 1 – hidden column in a virtual table; 2 – dynamic generated column, or 3 – stored generated column).
PRAGMA schema.index_list(table-name);	Returns one row for each index on the specified table. The output shows an internal sequence number, index name, uniqueness flag (1 if unique, 0 if not), origin (c – created by a CREATE INDEX statement, u – created by a UNIQUE constraint, or pk – created by a PRIMARY KEY constraint), and a flag indicating whether the index is partial (1 if partial, 0 if not).
PRAGMA schema.index_info(index-name);	Returns one row for each key column (explicitly named in a CREATE INDEX statement or included in a UNIQUE or PRIMARY KEY constraint that creates an index) in the specified index. The output shows the column position in the index (0 – the leftmost), position in the table (-1 for rowid, or -2 for an expression), and the column name (NULL for rowid or expression keys). Auxiliary columns are not included (see PRAGMA index_xinfo for those). If the specified index doesn’t exist but there’s a WITHOUT ROWID table with the same name, this PRAGMA statement returns the PRIMARY KEY columns of that table, with duplicate columns removed.
PRAGMA schema.index_xinfo(index-name);	Returns one row for each column in the specified index, including auxiliary columns. The output shows the column position in the index (0 – the leftmost; auxiliary columns follow key columns), position in the table (-1 for rowid, or -2 for an expression), column name (NULL for rowid or expression keys), sort order (1 if sorted in DESC order, 0 otherwise), collation name, and a flag distinguishing columns (1 – a key column, 0 – an auxiliary column). If the specified index doesn’t exist but there’s a WITHOUT ROWID table with the same name, this PRAGMA statement returns the columns of that table, with data columns following PRIMARY KEY columns and duplicate columns removed.
PRAGMA foreign_key_list(table-name);	Returns one row for each foreign key defined on the specified table.
PRAGMA function_list;	Returns a list of SQL functions known to the connection. Functions with multiple signatures (for example, if they can be invoked with a varying number of arguments or can accept text in various encodings) appear in multiple rows.

For official SQLite documentation, see PRAGMA Statements.