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

这个Stackoverflow条目建议执行选择

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

并解析结果。

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


当前回答

     //Called when application is started. It works on Droidscript, it is tested
     function OnStart()
     {
     //Create a layout with objects vertically centered. 
     lay = app.CreateLayout( "linear", "VCenter,FillXY" );  

     //Create a text label and add it to layout.
     txt = app.CreateText( "", 0.9, 0.4, "multiline" )  
     lay.AddChild( txt );
     app.AddLayout(lay);

     db = app.OpenDatabase( "MyData" )  
  
     //Create a table (if it does not exist already).  
     db.ExecuteSql( "drop table if exists test_table" )
     db.ExecuteSql( "CREATE TABLE IF NOT EXISTS test_table " +  
       "(id integer primary key, data text, num integer)",[],null, OnError )  
        db.ExecuteSql( "insert into test_table values (1,'data10',100), 
        (2,'data20',200),(3,'data30',300)")
        //Get all the table rows.      
        DisplayAllRows("SELECT * FROM test_table");
        DisplayAllRows("select *, id+100 as idplus, 'hahaha' as blabla from 
        test_table order by id desc;") 
     }

//function to display all records 
function DisplayAllRows(sqlstring)  // <-- can you use for any table not need to 
                                //  know column names, just use a *
                                // example: 
{ 
//Use all rows what is in ExecuteSql  (try any, it will works fine)
db.ExecuteSql( sqlstring, [], OnResult, OnError ) 
} 
//Callback to show query results in debug.  
function OnResult( res )   
{  
var len = res.rows.length; 
var s = txt.GetText();  
// ***********************************************************************
// This is the answer how to read column names from table:
for(var ColumnNames in res.rows.item(0)) s += " [ "+ ColumnNames +" ] "; // "[" & "]" optional, i use only in this demo 
// ***********************************************************************
//app.Alert("Here is all Column names what Select from your table:\n"+s);
s+="\n";
for(var i = 0; i < len; i++ )   
{  
    var rows = res.rows.item(i) 
    for (var item in rows) 
        {
            s += "    " + rows[item] + "   ";
        }
    s+="\n\n";
} 
//app.Alert(s);
txt.SetText( s )  
}  
//Callback to show errors.  
function OnError( msg )   
{  
   app.Alert( "Error: " + msg )  
}  

其他回答

如果您正在使用SQLite3,则不支持INFORMATION_SCHEMA。使用PRAGMA table_info代替。这将返回关于表的6行信息。要获取列名(row2),使用如下所示的for循环

cur.execute("PRAGMA table_info(table_name)")  # fetches the 6 rows of data
records = cur.fetchall() 
print(records)
for row in records:
    print("Columns: ", row[1])

. schema table_name

这将从数据库中列出表的列名。

希望这能有所帮助!!

我能够通过使用一个sql查询检索具有相应列的表名,但列输出是逗号分隔的。我希望它能帮助到一些人

SELECT tbl_name, (SELECT GROUP_CONCAT(name, ',') FROM PRAGMA_TABLE_INFO(tbl_name)) as columns FROM sqlite_schema WHERE type = 'table';

这是一个老问题,但这里有一个替代答案,检索SQLite数据库中的所有列,并为每列提供关联表的名称:

WITH tables AS (SELECT name tableName, sql 
FROM sqlite_master WHERE type = 'table' AND tableName NOT LIKE 'sqlite_%')
SELECT fields.name, fields.type, tableName
FROM tables CROSS JOIN pragma_table_info(tables.tableName) fields

返回这样的结果:

{
    "name": "id",
    "type": "integer",
    "tableName": "examples"
}, {
    "name": "content",
    "type": "text",
    "tableName": "examples"
}

对于包含标识符和字符串内容的简单表。

如果你想要查询的输出包含列名并正确对齐为列,可以在sqlite3中使用这些命令:

.headers on
.mode column

你会得到如下输出:

sqlite> .headers on
sqlite> .mode column
sqlite> select * from mytable;
id          foo         bar
----------  ----------  ----------
1           val1        val2
2           val3        val4