如何获取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.

我想把我的iPhone应用程序迁移到一个新的数据库版本。由于我没有保存一些版本，所以需要检查是否存在某些列名称。

This * entry suggests doing the select

这个*条目建议执行select。

SELECT sql FROM sqlite_master
WHERE tbl_name = 'table_name' AND type = 'table'

and parse the result.

和解析结果。

Is that the common way? Alternatives?

这是常见的方法吗?选择呢?

12 个解决方案

#1

451

PRAGMA table_info(table_name);

will get you a list of all the column names.

将为您提供所有列名的列表。

#2

155

If you have the sqlite database, use the sqlite3 command line program and these commands:

如果您有sqlite数据库，请使用sqlite3命令行程序和以下命令:

To list all the tables in the database:

列出数据库中的所有表:

.tables

To show the schema for a given tablename:

显示给定表名的模式:

.schema tablename

#3

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:

其中id和名称是列的实际名称。为了得到这个值，你需要选择列名:

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

#4

If you do

如果你做

.headers ON

you will get the desired result.

你会得到想要的结果。

#5

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

如果搜索任何特定的列，可以使用Like语句。

ex:

例:

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

#6

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

#7

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

#8

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

#9

.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)

#10

When you run the sqlite3 cli, typing in:

当您运行sqlite3 cli时，键入:

sqlite3 -header

will also give the desired result

还会给出想要的结果吗?

#11

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.

要查找表的列名，您应该执行“从tbl_name中选择*”，您将得到“sqlite3_stmt *”的结果。并检查列迭代的总获取列。请参考下面的代码。

// 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.

这将打印结果集的所有列名。

#12

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

#1

451

PRAGMA table_info(table_name);