SQLite Forum

Retrieve query schema without executing query
Login

Retrieve query schema without executing query

(1) By Patrick DeVivo (patrickdevivo) on 2021-02-20 22:08:15 [link] [source]

Hi there! I'm wondering if there's a way to retrieve the schema (columns + types) of a SQLite query without actually executing the query. I know it may be possible to tack a LIMIT 0 onto the end of the query, but this seems unideal and hard to rely on programmatically (if the queries I want to retrieve the schema for include their own LIMIT clauses).

I was wondering if there's a built-in function or maybe a way to access the query parser directly to retrieve this information, before a query is executed.

Thanks so much!

(2.1) By Larry Brasfield (larrybr) on 2021-02-20 22:18:04 edited from 2.0 in reply to 1 [source]

See pragma table_xinfo.

You may need to adjust build options to have this available.

(edit .1:) Stephan's answer reminds me: Once you have prepared a statement which may return query results, and before sqlite3_step()'ing it, the column names are available via a sqlite3_column_count() call followed by sqlite3_column_name() calls with indices suggested by the count.

(3) By Stephan Beal (stephan) on 2021-02-20 22:13:35 in reply to 1 [link] [source]

I'm wondering if there's a way to retrieve the schema (columns + types) of a SQLite query without actually executing the query.

For tables you can use the table_info pragma:

https://www.sqlite.org/pragma.html#pragma_table_info

For arbitrary queries there cannot really be such a construct which returns the data type because any given column can hold any given data type in any given row. The data type declarations in a table are hints, not directly enforced by the db (but can be enforced with manually-installed constraints).

(4) By Patrick DeVivo (patrickdevivo) on 2021-02-20 22:15:02 in reply to 2.0 [link] [source]

Thanks! I saw this, but from what I can tell it only returns the schema of a table vs a query - I'm looking for a way to retrieve what the columns/types of a query would be before executing it (it may join tables or select only a handful of columns).

Maybe I'll have to define the query as a view and then can use the table_xinfo pragma

(5) By Larry Brasfield (larrybr) on 2021-02-20 22:19:31 in reply to 4 [link] [source]

See my edit in post 2.

(6) By Keith Medcalf (kmedcalf) on 2021-02-20 23:09:01 in reply to 4 [link] [source]

You cannot. Each value (the "thing" at the intersection of a row and column) may be of any "type" whatsoever. This applies to tables, views, and queries.

You can request the "declared type" which will return the "declared type" of the underlying table column, if it is a table column (and not an expression), otherwise nothing. The "declared type" however does not constraint the actual type of the value of any particular instance (row) of that column, although you may impose artificial constraints on the particular types which may be stored.