Find SQLite Column Names in Empty Table

Question

For kicks I'm writing a "schema documentation" tool that generates a description of the tables and relationships in a database. I'm currently shimming it to work with SQLite.

I've managed to extract the names of all the tables in a SQLite database via a query on the sqlite_master table. For each table name, I then fire off a simple

select * from <table name>

query, then use the sqlite3_column_count() and sqlite3_column_name() APIs to collect the column names, which I further feed to sqlite3_table_column_metadata() to get additional info. Simple enough, right?

The problem is that it only works for tables that are not empty. That is, the sqlite_column_*() APIs are only valid if sqlite_step() has returned SQLITE_ROW, which is not the case for empty tables.

So the question is, how can I discover column names for empty tables? Or, more generally, is there a better way to get this type of schema info in SQLite?

I feel like there must be another hidden sqlite_xxx table lurking somewhere containing this info, but so far have not been able to find it.

Solution 1

Execute the query:

PRAGMA table_info( your_table_name );

Documentation

Solution 2

PRAGMA table_info( your_table_name ); doesn't work in HTML5 SQLite.

Here is a small HTML5 SQLite JavaScript Snippet which gets the column names from your_table_name even if its empty. Hope its helpful.

tx.executeSql('SELECT name, sql FROM sqlite_master WHERE type="table" AND name = "your_table_name";', [], function (tx, results) {
  var columnParts = results.rows.item(0).sql.replace(/^[^\(]+\(([^\)]+)\)/g, '$1').split(',');
  var columnNames = [];
  for(i in columnParts) {
    if(typeof columnParts[i] === 'string')
      columnNames.push(columnParts[i].split(" ")[0]);
  }
  console.log(columnNames);
  ///// Your code which uses the columnNames;
});

Solution 3

Execute this query

select * from (select "") left join my_table_to_test b on -1 = b.rowid;

You can try it at online sqlite engine

Solution 4

The PRAGMA statement suggested by @pragmanatu works fine through any programmatic interface, too. Alternatively, the sql column of sqlite_master has the SQL statement CREATE TABLE &c &c that describes the table (but, you'd have to parse that, so I think PRAGMA table_info is more... pragmatic;-).

View more solutions