PRAGMA statements are specialized SQL statements in SQLite. On one hand, they return tabular results similar to a regular SELECT statement. On the other hand, when using a PRAGMA statement, it’s impossible to limit the list of columns that the PRAGMA statement should return, or to specify any selection constraints such as WHERE.
However, when full-featured metadata querying is needed, SQLite provides a set of table-valued functions. These functions have the same names as their corresponding PRAGMA statements, but with a pragma_ prefix. Table-valued functions are available for PRAGMA statements that return results and have no side effects.
For example, this statement returns a table with a complete description of all columns in the dept table:
PRAGMA table_info('dept')
But the following statement allows you to select only the names of columns that have the INTEGER type:
SELECT name
FROM pragma_table_info('dept')
WHERE type = 'INTEGER'
This approach allows you to use standard SQL clauses such as WHERE to filter results, select only specific columns you need, apply aggregate functions, or join metadata results to other tables. This makes working with SQLite database metadata much more flexible and convenient compared to PRAGMA statements.