How to get a list of column names

Solution 1:

SELECT sql FROM sqlite_master
WHERE tbl_name = 'table_name' AND type = 'table'

Then parse this value with Reg Exp (it's easy) which could looks similar to this: [(.*?)]

Alternatively you can use:

PRAGMA table_info(table_name)

Solution 2:

If you are using the command line shell to SQLite then .headers on before you perform your query. You only need to do this once in a given session.

Solution 3:

Yes, you can achieve this by using the following commands:

sqlite> .headers on
sqlite> .mode column

The result of a select on your table will then look like:

id          foo         bar         age         street      address
----------  ----------  ----------  ----------  ----------  ----------
1           val1        val2        val3        val4        val5
2           val6        val7        val8        val9        val10

Solution 4:

You can use pragma related commands in sqlite like below

pragma table_info("table_name")
--Alternatively
select * from pragma_table_info("table_name")

If you require column names like id|foo|bar|age|street|address, basically your answer is in below query.

select group_concat(name,'|') from pragma_table_info("table_name")

Solution 5:

This helps for HTML5 SQLite:

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(','); ///// RegEx
  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;
});

You can reuse the regex in your language to get the column names.

Shorter Alternative:

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