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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Yes  you can achieve this by using the following commands   sqlite gt   headers on sqlite gt   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

User · Answer

Using  Tarkus s answer  here are the regexes I used in R   getColNames  lt - function conn  tableName        x  lt - dbGetQuery  conn  paste0  SELECT sql FROM sqlite master WHERE tbl name      tableName    AND type    table      1 1      x  lt - str split x    n    1   -1      x  lt - sub           x      res  lt - gsub         str extract  x 1                  x  lt - x -1      x  lt - x -length x       res  lt - c  res  gsub     t       str extract  x     t 0-9a-zA-Z               res     Code is somewhat sloppy  but it appears to work

User · Answer

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

User · Answer

lt    db   sqlite open  mysqlitedb     cols   sqlite fetch column types  form name  db  SQLITE ASSOC   foreach   cols as  column   gt   type      echo  Column   column  Type   type n

User · Answer

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

User · Answer

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

[sqlite] How to get a list of column names

Examples related to sqlite