How to get a list of column names on Sqlite3 datab

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?

Answer 1

-(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, &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;
  }

Answer 2

you can use Like statement if you are searching for any particular column

ex:

SELECT * FROM sqlite_master where sql like('%LAST%')

Answer 3

I know it is an old thread, but recently I needed the same and found a neat way:

select t.tbl_name, c.name col_name
from 
  sqlite_master t, 
  pragma_table_info(t.tbl_name) c
where t.type = 'table';

Answer 4

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, &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
    }
}

Answer 5

//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, &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;
}

Answer 6

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