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?
This question is related to
sqlite
-(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;
}
function getDetails(){
var data = [];
dBase.executeSql("PRAGMA table_info('table_name') ", [], function(rsp){
if(rsp.rows.length > 0){
for(var i=0; i<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));
});
}
If all else fails, you can always submit a query, limiting the return rows to none:
select * from MYTABLENAME limit 0
.schema table_name
This will list down the column names of the table from the database.
Hope this will help!!!
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;
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:
.schema in sqlite console when you have you're inside the table it looks something like this for me ...
sqlite>.schema
CREATE TABLE players(
id integer primary key,
Name varchar(255),
Number INT,
Team varchar(255)
When you run the sqlite3
cli, typing in:
sqlite3 -header
will also give the desired result
//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;
}
you can use Like statement if you are searching for any particular column
ex:
SELECT * FROM sqlite_master where sql like('%LAST%')
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, &statement_showHeader, NULL);
int headerColumnSize = sqlite3_column_count(statement_showHeader);
NSString* headerRow = @"|";
for (int j = 0; j < 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 ---------------------
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> .headers on
sqlite> .mode column
sqlite> select * from mytable;
id foo bar
---------- ---------- ----------
1 val1 val2
2 val3 val4
PRAGMA table_info(table_name);
will get you a list of all the column names.
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 <= mrs.getColumnCount(); i++)
{
Object row[] = new Object[3];
row[0] = mrs.getColumnLabel(i);
row[1] = mrs.getColumnTypeName(i);
row[2] = mrs.getPrecision(i);
}
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
}
}
To get a list of columns you can simply use:
.schema tablename
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
If you do
.headers ON
you will get the desired result.
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<totalColumn; iterator++) {
NSLog(@"%s", sqlite3_column_name(statement, iterator));
}
This will print all the column names of the result set.
Source: Stackoverflow.com