SQLite Schema Information Metadata

I need to get column names and their tables in a SQLite database. What I need is a resultset with 2 columns: table_name | column_name.

In MySQL, I'm able to get this information with a SQL query on database INFORMATION_SCHEMA. However the SQLite offers table sqlite_master:

sqlite> create table students (id INTEGER, name TEXT);
sqlite> select * from sqlite_master;
  table|students|students|2|CREATE TABLE students (id INTEGER, name TEXT)

which results a DDL construction query (CREATE TABLE) which is not helpful for me and I need to parse this to get relevant information.

I need to get list of tables and join them with columns or just get columns along with table name column. So PRAGMA table_info(TABLENAME) is not working for me since I don't have table name. I want to get all column metadata in the database.

Is there a better way to get that information as a result set by querying database?

You've basically named the solution in your question.

To get a list of tables (and views), query sqlite_master as in

SELECT name, sql FROM sqlite_master
WHERE type='table'
ORDER BY name;

(see the SQLite FAQ)

To get information about the columns in a specific table, use PRAGMA table_info(table-name); as explained in the SQLite PRAGMA documentation.

I don't know of any way to get tablename|columnname returned as the result of a single query. I don't believe SQLite supports this. Your best bet is probably to use the two methods together to return the information you're looking for - first get the list of tables using sqlite_master, then loop through them to get their columns using PRAGMA table_info().

Recent versions of SQLite allow you to select against PRAGMA results now, which makes this easy:

SELECT 
  m.name as table_name, 
  p.name as column_name
FROM 
  sqlite_master AS m
JOIN 
  pragma_table_info(m.name) AS p
ORDER BY 
  m.name, 
  p.cid

where p.cid holds the column order of the CREATE TABLE statement, zero-indexed.

David Garoutte answered this here, but this SQL should execute faster, and columns are ordered by the schema, not alphabetically.

Note that table_info also contains

type (the datatype, like integer or text),
notnull (1 if the column has a NOT NULL constraint)
dflt_value (NULL if no default value)
pk (1 if the column is the table's primary key, else 0)

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

There are ".tables" and ".schema [table_name]" commands which give kind of a separated version to the result you get from "select * from sqlite_master;"

There is also "pragma table_info([table_name]);" command to get a better result for parsing instead of a construction query:


sqlite> .tables
students
sqlite> .schema students
create table students(id INTEGER, name TEXT);
sqlite> pragma table_info(students);
0|id|INTEGER|0||0
1|name|TEXT|0||0

Hope, it helps to some extent...

Another useful trick is to first get all the table names from sqlite_master.

Then for each one, fire off a query "select * from t where 1 = 0". If you analyze the structure of the resulting query - depends on what language/api you're calling it from - you get a rich structure describing the columns.

In python

c = ...db.cursor()
c.execute("select * from t where 1=0");
c.fetchall();
print c.description;

Juraj

PS. I'm in the habit of using 'where 1=0' because the record limiting syntax seems to vary from db to db. Furthermore, a good database will optimize out this always-false clause.

The same effect, in SQLite, is achieved with 'limit 0'.

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).

SQLite Schema Information Metadata

Related

Recent Posts