How to get a list of column names on Sqlite3 database

Question

I want to migrate my iPhone app to a new database version  Since I don t have some version saved  I need to check if certain column names exist   This Stackoverflow entry suggests doing the select  SELECT sql FROM sqlite master WHERE tbl name    table name  AND type    table    and parse the result    Is that the common way  Alternatives

User · Answer

I know it is an old thread  but recently I needed the same and found a neat way   SELECT c name FROM pragma table info  your table name   c

User · Answer

function getDetails    var data       dBase executeSql  PRAGMA table info  table name          function rsp       if rsp rows length  gt  0           for var i 0  i lt rsp rows length  i                 var o                     name  rsp rows item i  name                  type  rsp rows item i  type                            data push o                       alert rsp rows item 0  name      function error       alert JSON stringify error

User · Answer

If you have the sqlite database  use the sqlite3 command line program and these commands   To list all the tables in the database    tables   To show the schema for a given tablename    schema tablename

User · Answer

If you want the output of your queries to include columns names and be correctly aligned as columns  use these commands in sqlite3    headers on  mode column   You will get output like   sqlite gt   headers on sqlite gt   mode column sqlite gt  select   from mytable  id          foo         bar ----------  ----------  ---------- 1           val1        val2 2           val3        val4

User · Answer

Just for super noobs like me wondering how or what people meant by   PRAGMA table info  table name      You want to use use that as your prepare statement as shown below  Doing so selects a table that looks like this except is populated with values pertaining to your table   cid         name        type        notnull     dflt value  pk         ----------  ----------  ----------  ----------  ----------  ---------- 0           id          integer     99                      1          1           name                    0                       0   Where id and name are the actual names of your columns  So to get that value you need to select column name by using     returns the name sqlite3 column text stmt  1     returns the type sqlite3 column text stmt  2     Which will return the current row s column s name  To grab them all or find the one you want you need to iterate through all the rows  Simplest way to do so would be in the manner below     where rc is an int variable if wondering    rc   sqlite3 prepare v2 dbPointer   pragma table info   your table name goes here     -1   amp stmt  NULL    if  rc  SQLITE OK          will continue to go down the rows  columns in your table  till there are no more     while sqlite3 step stmt     SQLITE ROW                sprintf colName    s   sqlite3 column text stmt  1              do something with colName because it contains the column s name

User · Answer

To get a list of columns you can simply use    schema tablename

User · Answer

If you do   headers ON   you will get the desired result

User · Answer

When you run the sqlite3 cli  typing in   sqlite3 -header   will also give the desired result

User · Answer

If all else fails  you can always submit a query  limiting the return rows to none  select   from MYTABLENAME limit 0

User · Answer

JUST little bit modified the answer of giuseppe  which returns array of table columns   NSMutableArray  tableInfo  NSString   table       sqlite3 stmt  sqlStatement       NSMutableArray  result    NSMutableArray array        const char  sql     NSString stringWithFormat   PRAGMA table info        table  UTF8String        if sqlite3 prepare md database  sql  -1   amp sqlStatement  NULL     SQLITE OK                 NSLog   Problem with prepare statement tableInfo                       NSString stringWithUTF8String  const char   sqlite3 errmsg md database                 while  sqlite3 step sqlStatement   SQLITE ROW                 result addObject             NSString stringWithUTF8String  char  sqlite3 column text sqlStatement  1                return result

User · Answer

An alternative way to get a list of column names not mentioned here is to select from a pragma function   SELECT name FROM PRAGMA TABLE INFO  your table    name       tbl name   rootpage   sql   You can check if a certain column exists by running   SELECT 1 FROM PRAGMA TABLE INFO  your table   WHERE name  sql   1   This is what you use if you don t want to parse the result of select sql from sqlite master or pragma table info   Reference   https   www sqlite org pragma html pragfunc

User · Answer

schema  in sqlite console when you have you re inside the table  it looks something like this for me       sqlite gt  schema CREATE TABLE players  id integer primary key  Name varchar 255   Number INT  Team varchar 255

User · Answer

- NSMutableDictionary  tableInfo  NSString   table     sqlite3 stmt  sqlStatement    NSMutableDictionary  result     NSMutableDictionary alloc  init     const char  sql     NSString stringWithFormat   pragma table info   s     table UTF8String   UTF8String     if sqlite3 prepare db  sql  -1   amp sqlStatement  NULL     SQLITE OK          NSLog   Problem with prepare statement tableInfo      NSString stringWithUTF8String  const char   sqlite3 errmsg db            while  sqlite3 step sqlStatement   SQLITE ROW           result setObject     forKey  NSString stringWithUTF8String  char  sqlite3 column text sqlStatement  1             return result

User · Answer

I know it s too late but this will help other   To find the column name of the table  you should execute select   from tbl name and you will get the result in sqlite3 stmt    and check the column iterate over the total fetched column  Please refer following code for the same      sqlite3 stmt  statement   int totalColumn   sqlite3 column count statement   for  int iterator   0  iterator lt totalColumn  iterator         NSLog    s   sqlite3 column name statement  iterator        This will print all the column names of the result set

User · Answer

Maybe you just want to print the table headers on the console  This is my code   for each table          ------------------ show header ----------------       char sqlite stmt showHeader 1000       snprintf sqlite stmt showHeader  1000    s s    SELECT   FROM    TABLE NAME STRING UTF8String         sqlite3 stmt  statement showHeader      sqlite3 prepare v2 DATABASE  sqlite stmt showHeader  -1   amp statement showHeader  NULL        int headerColumnSize   sqlite3 column count statement showHeader        NSString  headerRow              for  int j   0  j  lt  headerColumnSize  j              NSString  headerColumnContent     NSString alloc  initWithUTF8String  const char  sqlite3 column name statement showHeader  j            headerRow     NSString alloc  initWithFormat             headerRow  headerColumnContent              NSLog        headerRow         sqlite3 finalize statement showHeader           ---------------- show header end ---------------------

User · Answer

PRAGMA table info table name     will get you a list of all the column names

User · Answer

schema table name  This will list down the column names of the table from the database   Hope this will help

User · Answer

you can use Like statement if you are searching for any particular column  ex   SELECT   FROM sqlite master where sql like   LAST

User · Answer

In order to get the column information you can use the following snippet   String sql    select   from   oTablename   LIMIT 0   Statement statement   connection createStatement    ResultSet rs   statement executeQuery sql   ResultSetMetaData mrs   rs getMetaData    for int i   1  i  lt   mrs getColumnCount    i          Object row     new Object 3       row 0    mrs getColumnLabel i       row 1    mrs getColumnTypeName i       row 2    mrs getPrecision i

[sqlite] How to get a list of column names on Sqlite3 database?

Examples related to sqlite