How to get a list of column names on sqlite3 / iPhone?
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?
PRAGMA table_info(table_name);
会为您提供所有列名称的列表。
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
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
}
}