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

Solution 1

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.

Solution 2

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

Solution 3

FYI, if you're using .Net you can use the DbConnection.GetSchema method to retrieve information that usually is in INFORMATION_SCHEMA. If you have an abstraction layer you can have the same code for all types of databases (NOTE that MySQL seems to swich the 1st 2 arguments of the restrictions array).

Comments

[Jhonny D. Cano -Leftware-]

broken link, and i'm looking for a native way