我想把我的iPhone应用程序迁移到一个新的数据库版本。由于我没有保存一些版本,我需要检查是否存在某些列名。
这个Stackoverflow条目建议执行选择
SELECT sql FROM sqlite_master
WHERE tbl_name = 'table_name' AND type = 'table'
并解析结果。
这是常见的方式吗?选择呢?
我想把我的iPhone应用程序迁移到一个新的数据库版本。由于我没有保存一些版本,我需要检查是否存在某些列名。
这个Stackoverflow条目建议执行选择
SELECT sql FROM sqlite_master
WHERE tbl_name = 'table_name' AND type = 'table'
并解析结果。
这是常见的方式吗?选择呢?
当前回答
我知道已经太迟了,但这会帮助其他人。
要找到表的列名,您应该执行select * from 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));
}
这将打印结果集的所有列名。
其他回答
我能够通过使用一个sql查询检索具有相应列的表名,但列输出是逗号分隔的。我希望它能帮助到一些人
SELECT tbl_name, (SELECT GROUP_CONCAT(name, ',') FROM PRAGMA_TABLE_INFO(tbl_name)) as columns FROM sqlite_schema WHERE type = 'table';
如果您正在使用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])
我知道这是一个旧的帖子,但最近我需要同样的帖子,并找到了一个简洁的方法:
SELECT c.name FROM pragma_table_info('your_table_name') c;
//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 )
}
获取一个表和列的列表作为视图:
CREATE VIEW Table_Columns AS
SELECT m.tbl_name AS TableView_Name, m.type AS TableView, cid+1 AS Column, p.*
FROM sqlite_master m, Pragma_Table_Info(m.tbl_name) p
WHERE m.type IN ('table', 'view') AND
( m.tbl_name = 'mypeople' OR m.tbl_name LIKE 'US_%') -- filter tables
ORDER BY m.tbl_name;