How to get a list of column names

Question

Is it possible to get a row with all column names of a table like this?

|id|foo|bar|age|street|address|

I don't like to use Pragma table_info(bla).

Answer 1

select * from tablename limit 0

next, cycle through the fields in the returned dataset

Answer 2

If we assume you are on Unix you can use

sqlite3 -header your_database.sqlite "SELECT * FROM your_table LIMIT 1;" | head -n1

This prints the header and 1 row to stdout then we just select the first line.

The default output is list and the default separator is |, which gives the desired output. Both can be configured with command line arguments: sqlite3 --help

If you do require both leading and trailing |, then you can do

sqlite3 -header your_database.sqlite "SELECT * FROM your_table LIMIT 1;" | head -n1 | sed "s/^/|/;s/$/|/"

which does the two substitutions with sed.

Answer 3

1. with the following query, combining sqlite_master for the name of the tables and pragma_table_info for the description of all the tables gives the complete structure of ALL the tables in a single query.

    SELECT sm.tname AS tablename, p.* FROM sqlite_master sm, pragma_table_info(sm.name) p
    WHERE sm.type = 'table'  -- just selecting the tables in sqlite_master
    ORDER BY sm.name, p.cid;  -- cid in pragma_table_info 

2. Then, making a CTE

    WITH firstloop AS (SELECT sm.name AS tablename, p.* 
    FROM sqlite_master sm, pragma_table_info(sm.name) p
               WHERE sm.type = 'table'
    ORDER BY sm.name, p.cid)
    SELECT tablename, 
    '|' || GROUP_CONCAT(name, '|') || '|' AS aggregatedlist FROM firstloop
    GROUP BY tablename;

Hopes this helps.

Answer 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")

Answer 5

Use a recursive query. Given

create table t (a int, b int, c int);

Run:

with recursive
  a (cid, name) as (select cid, name from pragma_table_info('t')),
  b (cid, name) as (
    select cid, '|' || name || '|' from a where cid = 0
    union all
    select a.cid, b.name || a.name || '|' from a join b on a.cid = b.cid + 1
  )
select name
from b
order by cid desc
limit 1;

Alternatively, just use group_concat:

select '|' || group_concat(name, '|') || '|' from pragma_table_info('t')

Both yield:

|a|b|c|

Answer 6

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

Answer 7

Easiest way to get the column names of the most recently executed SELECT is to use the cursor's description property. A Python example:

print_me = "("
for description in cursor.description:
    print_me += description[0] + ", "
print(print_me[0:-2] + ')')
# Example output: (inp, output, reason, cond_cnt, loop_likely)

Answer 8

Try this sqlite table schema parser, I implemented the sqlite table parser for parsing the table definitions in PHP.

It returns the full definitions (unique, primary key, type, precision, not null, references, table constraints... etc)

https://github.com/maghead/sqlite-parser

Answer 9

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)

Answer 10

The result set of a query in PHP offers a couple of functions allowing just that:

    numCols() 
    columnName(int $column_number )

Example

    $db = new SQLIte3('mysqlite.db');
    $table = 'mytable';

    $tableCol = getColName($db, $table);

    for ($i=0; $i<count($tableCol); $i++){
        echo "Column $i = ".$tableCol[$i]."\n";
    }           

    function getColName($db, $table){
        $qry = "SELECT * FROM $table LIMIT 1";
        $result = $db->query($qry);
        $nCols = $result->numCols();
        for ($i = 0; $i < $ncols; $i++) {
            $colName[$i] = $result->columnName($i);
        }
        return $colName;
    }

Answer 11

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.

Answer 12

Using @Tarkus's answer, here are the regexes I used in R:

getColNames <- function(conn, tableName) {
    x <- dbGetQuery( conn, paste0("SELECT sql FROM sqlite_master WHERE tbl_name = '",tableName,"' AND type = 'table'") )[1,1]
    x <- str_split(x,"\\n")[[1]][-1]
    x <- sub("[()]","",x)
    res <- gsub( '"',"",str_extract( x[1], '".+"' ) )
    x <- x[-1]
    x <- x[-length(x)]
    res <- c( res, gsub( "\\t", "", str_extract( x, "\\t[0-9a-zA-Z_]+" ) ) )
    res
}

Code is somewhat sloppy, but it appears to work.

Answer 13

$<?
$db = sqlite_open('mysqlitedb');
$cols = sqlite_fetch_column_types('form name'$db, SQLITE_ASSOC);
foreach ($cols as $column => $type) {
  echo "Column: $column  Type: $type\n";
}

Answer 14

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;
});