information_schema.columns on sqlite

Question

I would like to do a query like next:

SELECT table_name, column_name, data_type, is_nullable, ... 
FROM information_schema.columns

on a sqlite database.

I checked

PRAGMA table_info(table_name);

but doesn't suit my needs, just check fields for one table.

I checked

select * from sqlite_master where type = 'table';

But this just gets table names and creation query.

Is there any way to "join" these to methods? or any other suggestion or ideas? tx

Answer 1

There is much more support in the SQLite C API for this sort of thing; see this page for example.

Answer 2

I know you don't want to hear this, but you're not going to be able to do the join from SQL with SQLite. The table_info pragma is not mapped to a standard query, but rather a virtual machine program hard-coded into the SQLite sources. That program only supports a single table. Full stop. :)

If your needs are just testing, it shouldn't be too hard to write script to do what you want. Otherwise, you're going have to write this into your application. Either way, you'll be select the table name from sqlite_master using your sqlite_master query, make a SQL query from it using sqlite3_mprintf("pragma table_info(%s);",name), and prepare/execute that.