Learning › Ecsqlreference › Pragmas
Edit this page

Pragmas

PRAGMA help

Print out list of pragma supported by ECSQL.

PRAGMA help
pragma type descr
checksum global checksum([ecdb_schema|ecdb_map|sqlite_schema]) return sha3 checksum for data.
ecdb_ver global return current and file profile versions
experimental_features_enabled global enable/disable experimental features
validate_ecsql_writes global enable/disable validation for values in an ecsql statement
explain_query global explain query plan
help global return list of pragma supported
integrity_check global performs integrity checks on ECDb
parse_tree global parse_tree(ecsql) return parse tree of ecsql.
schema_view global returns a curated subset of schema metadata as a binary blob
disqualify_type_index class set/get disqualify_type_index flag for a given ECClass

PRAGMA checksum

Calculate and print out SHA3 256 hash for a subset of the ec_ tables or the sqlite schema

PRAGMA checksum('ecdb_map')
  • ecdb_schema - Includes only the ec definition tables but not the mapping tables

    ec_Schema, ec_SchemaReference, ec_Class, ec_ClassHasBaseClasses, ec_Enumeration, ec_KindOfQuantity, ec_UnitSystem, ec_Phenomenon, ec_Unit, ec_Format, ec_FormatCompositeUnit, ec_PropertyCategory, ec_Property, ec_RelationshipConstraint, ec_RelationshipConstraintClass, ec_CustomAttribute

  • ecdb_map - Includes only the ec mapping tables but not the ec definition tables

    ec_PropertyPath, ec_ClassMap, ec_Table, ec_Column, ec_Index, ec_IndexColumn, ec_PropertyMap

  • sqlite_schema - Includes information in the sqlite_master table

PRAGMA ecdb_ver

Print out ECDb current profile version supported by software and file profile version.

PRAGMA ecdb_ver
current file
4.0.0.4 4.0.0.2

PRAGMA ecsql_ver

Print out the current ECSQL version supported by the software. This will allow applications to check feature availability when working with ECSql.

PRAGMA ecsql_ver
ecsql_ver
2.0.3.1

PRAGMA sqlite_sql

Print out the underlying sqlite/native sql as a string. This will help debugging ECSql statements.

PRAGMA sqlite_sql([SELECT * FROM meta.ECClassDef WHERE Name='Element'])
sqlite_sql
SELECT [ECClassDef].[ECInstanceId],[ECClassDef].[ECClassId],[ECClassDef].[SchemaId],[ECClassDef].[SchemaRelECClassId],[ECClassDef].[Name],[ECClassDef].[DisplayLabel],[ECClassDef].[Description],[ECClassDef].[Type],[ECClassDef].[Modifier],[ECClassDef].[CustomAttributeContainerType],[ECClassDef].[RelationshipStrength],[ECClassDef].[RelationshipStrengthDirection] FROM (SELECT [Id] ECInstanceId,15 ECClassId,[SchemaId],(CASE WHEN [SchemaId] IS NULL THEN NULL ELSE 16 END) [SchemaRelECClassId],[Name],[DisplayLabel],[Description],[Type],[Modifier],[CustomAttributeContainerType],[RelationshipStrength],[RelationshipStrengthDirection] FROM [main].[ec_Class]) [ECClassDef] WHERE [ECClassDef].[Name]='Element'

PRAGMA experimental_features_enabled

Enable experimental feature in ECSQL on current connection.

PRAGMA experimental_features_enabled=true

to switch off

PRAGMA experimental_features_enabled=false

to check if flag is currently set.

PRAGMA experimental_features_enabled
experimental_features_enabled
False

PRAGMA validate_ecsql_writes

Enable validation of values in ECSQL insert statement on current connection.

PRAGMA validate_ecsql_writes=true

to switch off

PRAGMA validate_ecsql_writes=false

to check if flag is currently set.

PRAGMA validate_ecsql_writes
validate_ecsql_writes
False

PRAGMA explain_query

Prints out a high level description of the strategy or plan SQLite uses to implement a specific SQL query generated by the input ECSql query. For use in interactive debugging purposes, the output of this command may change in the future.

PRAGMA explain_query ('SELECT * FROM bis.GeometricElement3d')
id parent notused detail
3 0 215 SCAN main.bis_GeometricElement3d
5 0 45 SEARCH main.bis_Element USING INTEGER PRIMARY KEY (rowid=?)

PRAGMA integrity_check (experimental)

  1. check_ec_profile - checks if the profile table, indexes, and triggers are present. Does not check be_* tables. Issues are returned as a list of tables/indexes/triggers which were not found or have different DDL.
  2. check_data_schema - checks if all the required data tables and indexes exist for mapped classes. Issues are returned as a list of tables/columns which were not found or have different DDL.
  3. check_data_columns - checks if all the required columns exist in data tables. Issues are returned as a list of those tables/columns.
  4. check_nav_class_ids - checks if RelClassId of a Navigation property is a valid ECClassId. It does not check the value to match the relationship class.
  5. check_nav_ids - checks if Id of a Navigation property matches a valid row primary class.
  6. check_linktable_fk_class_ids - checks if SourceECClassId or TargetECClassId of a link table matches a valid ECClassId.
  7. check_linktable_fk_ids- checks if SourceECInstanceId or TargetECInstanceId of a link table matches a valid row in primary class.
  8. check_class_ids- checks persisted ECClassId in all data tables and makes sure they are valid.
  9. check_schema_load - checks if all schemas can be loaded into memory.
PRAGMA integrity_check ECSQLOPTIONS ENABLE_EXPERIMENTAL_FEATURES;

The output of integrety_check is a table with each test performed, the result and time took to run the test.

sno check result elapsed_sec
1 check_data_columns True 0.005
2 check_ec_profile True 0.001
3 check_nav_class_ids True 0.179
4 check_nav_ids True 0.403
5 check_linktable_fk_class_ids True 0.001
6 check_linktable_fk_ids False 0.003
7 check_class_ids True 0.039
8 check_data_schema True 0.000
9 check_schema_load True 0.000

PRAGMA parse_tree (experimental)

Generates a json representation parsed tree of the input ECSql.

PRAGMA parse_tree ('SELECT ECClassId, CodeValue FROM bis.GeometricElement3d') ECSQLOPTIONS ENABLE_EXPERIMENTAL_FEATURES
{ "id": "SelectStatementExp", "select": { "id": "SingleSelectStatementExp", "selection": [ { "id": "DerivedPropertyExp", "exp": { "id": "PropertyNameExp", "path": "ECClassId" } }, { "id": "DerivedPropertyExp", "exp": { "id": "PropertyNameExp", "path": "CodeValue" } } ], "from": [ { "id": "ClassNameExp", "tableSpace": "", "schemaName": "BisCore", "className": "GeometricElement3d" } ] } }

PRAGMA schema_view

Returns a curated subset of EC schema metadata from the connection as a single compact binary blob. This is used internally by SchemaView to hydrate a lightweight, read-only schema cache in a single round-trip instead of loading each schema individually. The subset is described in SchemaView - What is included / excluded.

The pragma accepts an optional integer argument to select the binary format version. When omitted, the latest supported version is returned (currently v1).

PRAGMA schema_view

Explicitly request format version 1:

PRAGMA schema_view(1)

The result is a single row with the following columns:

Column Type Description
format string Format identifier (currently binary)
formatVersion integer The format version of the returned blob
data binary The schema metadata blob
schemaToken string SHA3-256 hash of the ecdb_schema tables, usable as a cache-invalidation key

The pragma is read-only. Attempting to set a value returns an error.

Passing an unsupported format version returns an error:

-- ERROR: unsupported format version PRAGMA schema_view(99)

The pragma works against any ECDb profile from 4.0.0.1 onward; older files do not need to be upgraded first. On profile 4.0.0.1 only, KindOfQuantity persistence and presentation strings are returned in legacy FUS format rather than EC3.2; all other data is unaffected. See SchemaViewBinaryFormat - ECDb Profile Compatibility for details.

ECSql Syntax

Last Updated: 26 May, 2026