Displaying database information and internal parameters

The Database tab gives you a complete overview of the databases attached to the current connection, together with the SQLite settings (PRAGMAs) that control how each one behaves, and what the SQLite library itself offers you to write in SQL.

The tab has five pages. The first two are about this connection — what is attached to it, and how it is configured. The last three are about the SQLite library behind it, and list the three kinds of name you can write in SQL: functions, collations and virtual-table modules.

Databases

The Databases grid lists every internal database on the connection — main, temp, and any attached databases. For each one it shows:

• the file name (except for temp);
• the file size in bytes (except for temp and :memory: databases);
• the number of tables, views, indexes, and triggers;
• the connection status of the main database (connected, disconnected, encrypted, or unavailable).

Below the grid, Database properties shows the persistent properties of the database selected above — settings stored in the database file itself, such as the page size, the text encoding and the user version.

Connection

The Connection page shows the library, session and connection properties: settings that apply to the current connection only and are not saved in any file. compile_options lives here too, which is the definitive answer to what the loaded SQLite library was built with.

Both pages list SQLite PRAGMA commands. To change one, double-click the row (or select it and press Enter), type the new value, and click Apply.

Note

Not every property can be changed at will. The text encoding, for example, can only be set right after a database is created and before any tables exist. See the PRAGMA reference in the SQLite documentation for the details of each setting.

Functions

The Functions page lists every function this connection will answer to by name: the scalar, aggregate and window functions the library provides, plus anything registered at run time by a loaded extension or by SQLite Expert. It is a good first stop for is this available here? — whether regexp exists, or whether the extension you just loaded really registered what you expected.

Beside each name:

• Type — Scalar, Aggregate or Window.
• Args — how many arguments it takes. 2+ means two or more, any means any number, and 1, 2 means it accepts either.
• Built in — ticked for what the library itself provides, clear for anything registered at run time.
• Deterministic — a function that always returns the same answer for the same arguments. SQLite refuses a function that is not deterministic in an index, in a partial index's WHERE clause and in a generated column, so this column tells you in advance whether you can use one there.
• Direct only — a function that can only be called from a statement you run yourself. SQLite refuses it inside a view, a trigger, a CHECK constraint, a DEFAULT clause, an index and a generated column.
• Innocuous — a function with no side effects, which is safe in the schema even when it comes from a database you did not create. With trusted_schema turned off, SQLite treats every function that is not innocuous as if it were direct only.
• Subtype — the function reads the subtype of its arguments, as the JSON functions do to tell a JSON value from plain text.
• Result subtype — the function sets a subtype on its result.
• Self order — an aggregate that sorts the values of its first argument itself, such as median or percentile, and so can also be called as WITHIN GROUP (ORDER BY ...).

Note

Why Result subtype and Self order are usually clear. SQLite stores both flags but leaves them out of the list it gives SQLite Expert, unless the library was built to show internal details. The library that comes with SQLite Expert is not, so these two columns are clear for every function there. They fill in when you choose a different SQLite library in Options that does show them.

A name can appear more than once when the library offers it more than once: max is listed as both a scalar function and a window function, because those are two different functions that happen to share a name.

Note

Why nothing says Aggregate. SQLite decides this column by how a function was registered, not by how you use it, and every aggregate the library builds in is also usable as a window function. So sum, count, avg and group_concat all report as Window, and on a current library nothing reports as Aggregate at all. Only an extension that registers an aggregate the older way — one that cannot be used with OVER — produces that answer.

Note

A very old or unusual SQLite library may not be able to describe its own functions in that much detail. SQLite Expert hides the columns it cannot fill rather than showing them empty, so on such a library this page has fewer columns. The list of names itself is still complete.

Collations

The Collations page lists the collating sequences available for COLLATE clauses and for column definitions. A fresh connection has the three the library builds in — BINARY, NOCASE and RTRIM — and an extension can add its own.

Note

In the Professional edition the ICU collations do not appear here until they have been asked for: SQLite registers one the first time icu_load_collation is called for it. Call it, then reopen the page and the new name is listed.

Modules

The Modules page lists the virtual-table modules registered with this connection — the names you can write after CREATE VIRTUAL TABLE ... USING. This is where you find out what a particular build actually has: whether full-text search is available and at which version (fts3, fts4, fts5), whether rtree is there, and what a loaded extension has added.

All three of these pages are read again each time you open them, so an extension loaded while the tab is open appears the next time you look.

Maintenance commands

The Database tab also gives you quick access to the common maintenance commands — Integrity check, Vacuum, Vacuum Into..., Reindex, Repair, Recover, and Backup. These are described in Performing maintenance operations and Performing an online backup of a database.