Pragma statements supported by SQLite

[irthomasthomas]

• https://sqlite.org/pragma.html

Pragma statements supported by SQLite

Description

The PRAGMA statement is an SQL extension specific to SQLite and used to modify the operation of the SQLite library or to query the SQLite library for internal (non-table) data. The PRAGMA statement is issued using the same interface as other SQLite commands (e.g. SELECT, INSERT) but is different in the following important respects:

• The pragma command is specific to SQLite and is not compatible with any other SQL database engine.
• Specific pragma statements may be removed and others added in future releases of SQLite. There is no guarantee of backwards compatibility.
• No error messages are generated if an unknown pragma is issued. Unknown pragmas are simply ignored. This means if there is a typo in a pragma statement the library does not inform the user of the fact.
• Some pragmas take effect during the SQL compilation stage, not the execution stage. This means if using the C-language sqlite3_prepare(), sqlite3_step(), sqlite3_finalize() API (or similar in a wrapper interface), the pragma may run during the sqlite3_prepare() call, not during the sqlite3_step() call as normal SQL statements do. Or the pragma might run during sqlite3_step() just like normal SQL statements. Whether or not the pragma runs during sqlite3_prepare() or sqlite3_step() depends on the pragma and on the specific release of SQLite.
• The EXPLAIN and EXPLAIN QUERY PLAN prefixes to SQL statements only affect the behavior of the statement during sqlite3_step(). That means that PRAGMA statements that take effect during sqlite3_prepare() will behave the same way regardless of whether or not they are prefaced by "EXPLAIN".
• The C-language API for SQLite provides the SQLITE_FCNTL_PRAGMA file control which gives VFS implementations the opportunity to add new PRAGMA statements or to override the meaning of built-in PRAGMA statements.

PRAGMA Command Syntax

PRAGMA schema-name.pragma-name(pragma-value) = pragma-value

• A pragma can take either zero or one argument. The argument is may be either in parentheses or it may be separated from the pragma name by an equal sign. The two syntaxes yield identical results. In many pragmas, the argument is a boolean. The boolean can be one of:

  • 1, yes, true, on
  • 0, no, false, off

• Keyword arguments can optionally appear in quotes. (Example: 'yes' [FALSE].) Some pragmas takes a string literal as their argument. When pragma takes a keyword argument, it will usually also take a numeric equivalent as well. For example, "0" and "no" mean the same thing, as does "1" and "yes". When querying the value of a setting, many pragmas return the number rather than the keyword.

• A pragma may have an optional schema-name before the pragma name. The schema-name is the name of an ATTACH-ed database or "main" or "temp" for the main and the TEMP databases. If the optional schema name is omitted, "main" is assumed. In some pragmas, the schema name is meaningless and is simply ignored. In the documentation below, pragmas for which the schema name is meaningful are shown with a "schema." prefix.

PRAGMA Functions

PRAGMAs that return results and that have no side-effects can be accessed from ordinary SELECT statements as table-valued functions. For each participating PRAGMA, the corresponding table-valued function has the same name as the PRAGMA with a 7-character "pragma_" prefix. The PRAGMA argument and schema, if any, are passed as arguments to the table-valued function, with the schema as an optional, last argument.

For example, information about the columns in an index can be read using the index_info pragma as follows:

PRAGMA index_info('idx52');

Or, the same content can be read using:

SELECT * FROM pragma_index_info('idx52');

The advantage of the table-valued function format is that the query can return just a subset of the PRAGMA columns, can include a WHERE clause, can use aggregate functions, and the table-valued function can be just one of several data sources in a join. For example, to get a list of all indexed columns in a schema, one could query:

SELECT DISTINCT m.name || '.' || ii.name AS 'indexed-columns'
  FROM sqlite_schema AS m,
       pragma_index_list(m.name) AS il,
       pragma_index_info(il.name) AS ii
 WHERE m.type='table'
 ORDER BY 1;

Additional notes:

• Table-valued functions exist only for built-in PRAGMAs, not for PRAGMAs defined using the SQLITE_FCNTL_PRAGMA file control.
• Table-valued functions exist only for PRAGMAs that return results and that have no side-effects.
• This feature could be used to implement information schema by first creating a separate schema using

ATTACH ':memory:' AS 'information_schema';

Then creating VIEWs in that schema that implement the official information schema tables using table-valued PRAGMA functions.

The table-valued functions for PRAGMA feature was added in SQLite version 3.16.0 (2017-01-02). Prior versions of SQLite cannot use this feature.

More information

Suggested labels

{'label-name': 'SQL commands', 'label-description': 'Descriptions and syntax of SQL commands specific to SQLite.', 'confidence': 66.84}