Geek Answers Handbook

Information_schema.columns in sqlite

I would like to make the following query:

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

in sqlite database.

I checked

 PRAGMA table_info(table_name);

but doesn’t meet my needs, just check the fields for one table.

I checked

 select * from sqlite_master where type = 'table';

But it just gets the table names and the create request.

Is there a way to “attach” them to methods? or any other suggestions or ideas? TX

+7
database sqlite schema
source share
3 answers

I know that you do not want to hear this, but you cannot connect to SQL with SQLite. The table_info does not map to standard queries, but rather a virtual machine program that is hard-coded into SQLite sources. This program supports only one table. Full stop. :)

If your needs are just being tested, writing a script is not so difficult to do what you want. Otherwise, you will have to write this to your application. In any case, you will select the table name from sqlite_master using the sqlite_master query, make an SQL query using sqlite3_mprintf("pragma table_info(%s);",name) and prepare / execute this.

+4
source share

The SQLite C API has much more support for this kind of thing; see this page for example.

+3
source share

FYI, if you use .Net, you can use the DbConnection.GetSchema method to retrieve information that is usually found in INFORMATION_SCHEMA. If you have an abstraction level, you can have the same code for all types of databases (note that MySQL seems to swich the 1st 2nd argument of the constraint array).

+3
source share

More articles:


All Articles