SQLite Schema Information Metadata

Question

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?

Answer 1

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