database 在空表中查找 SQLite 列名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/928865/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Find SQLite Column Names in Empty Table
提问by Drew Hall
For kicks I'm writing a "schema documentation" tool that generates a description of the tables and relationships in a database. I'm currently shimming it to work with SQLite.
为了踢球，我正在编写一个“模式文档”工具，该工具生成数据库中表和关系的描述。我目前正在调整它以与 SQLite 一起使用。
I've managed to extract the names of all the tables in a SQLite database via a query on the sqlite_mastertable. For each table name, I then fire off a simple
我设法通过对sqlite_master表的查询提取了 SQLite 数据库中所有表的名称。对于每个表名，我然后触发一个简单的
select * from <table name>
query, then use the sqlite3_column_count()and sqlite3_column_name()APIs to collect the column names, which I further feed to sqlite3_table_column_metadata()to get additional info. Simple enough, right?
查询，然后使用sqlite3_column_count()和sqlite3_column_name()API 来收集列名称，我进一步提供这些名称sqlite3_table_column_metadata()以获取其他信息。够简单了吧？
The problem is that it only works for tables that are not empty. That is, the sqlite_column_*()APIs are only valid if sqlite_step()has returned SQLITE_ROW, which is not the case for empty tables.
问题是它只适用于非空的表。也就是说，sqlite_column_*()API 仅在sqlite_step()已返回时有效SQLITE_ROW，而空表则不是这种情况。
So the question is, how can I discover column names for empty tables? Or, more generally, is there a better way to get this type of schema info in SQLite?
所以问题是，如何发现空表的列名？或者，更一般地说，有没有更好的方法在 SQLite 中获取这种类型的架构信息？
I feel like there must be another hidden sqlite_xxxtable lurking somewhere containing this info, but so far have not been able to find it.
我觉得一定有另一个隐藏的sqlite_xxx桌子潜伏着包含这些信息的地方，但到目前为止还没有找到它。
回答by
sqlite> .header on
sqlite> .mode column
sqlite> create table ABC(A TEXT, B VARCHAR);
sqlite> pragma table_info(ABC);
cid name type notnull dflt_value pk
---------- ---------- ---------- ---------- ---------- ----------
0 A TEXT 0 0
1 B VARCHAR 0 0
回答by Nick Dandoulakis
回答by GeekTantra
PRAGMA table_info( your_table_name );doesn't work in HTML5 SQLite.
PRAGMA table_info( your_table_name );在 HTML5 SQLite 中不起作用。
Here is a small HTML5 SQLite JavaScript Snippet which gets the column names from your_table_name even if its empty. Hope its helpful.
这是一个小的 HTML5 SQLite JavaScript 代码段，它从 your_table_name 获取列名，即使它是空的。希望它有帮助。
tx.executeSql('SELECT name, sql FROM sqlite_master WHERE type="table" AND name = "your_table_name";', [], function (tx, results) {
var columnParts = results.rows.item(0).sql.replace(/^[^\(]+\(([^\)]+)\)/g, '').split(',');
var columnNames = [];
for(i in columnParts) {
if(typeof columnParts[i] === 'string')
columnNames.push(columnParts[i].split(" ")[0]);
}
console.log(columnNames);
///// Your code which uses the columnNames;
});
回答by user941581
Execute this query
执行此查询
select * from (select "") left join my_table_to_test b on -1 = b.rowid;
You can try it at online sqlite engine
您可以在在线sqlite引擎上尝试
回答by Alex Martelli
The PRAGMAstatement suggested by @pragmanatu works fine through any programmatic interface, too. Alternatively, the sqlcolumn of sqlite_masterhas the SQLstatement CREATE TABLE &c &cthat describes the table (but, you'd have to parse that, so I think PRAGMA table_infois more... pragmatic;-).
PRAGMA@pragmanatu 建议的语句也可以通过任何编程接口正常工作。或者，sql列sqlite_master有描述表的SQL语句CREATE TABLE &c &c（但是，你必须解析它，所以我认为PRAGMA table_info更......务实;-）。
回答by Alex Martelli
If you are suing SQLite 3.8.3 or later (supports the WITH clause), this recursive query should work for basic tables. On CTAS, YMMV.
如果您要起诉 SQLite 3.8.3 或更高版本（支持 WITH 子句），则此递归查询应该适用于基本表。在 CTAS，YMMV。
WITH
Recordify(tbl_name, Ordinal, Clause, Sql)
AS
(
SELECT
tbl_name,
0,
'',
Sql
FROM
(
SELECT
tbl_name,
substr
(
Sql,
instr(Sql, '(') + 1,
length(Sql) - instr(Sql, '(') - 1
) || ',' Sql
FROM
sqlite_master
WHERE
type = 'table'
)
UNION ALL
SELECT
tbl_name,
Ordinal + 1,
trim(substr(Sql, 1, instr(Sql, ',') - 1)),
substr(Sql, instr(Sql, ',') + 1)
FROM
Recordify
WHERE
Sql > ''
AND lower(trim(Sql)) NOT LIKE 'check%'
AND lower(trim(Sql)) NOT LIKE 'unique%'
AND lower(trim(Sql)) NOT LIKE 'primary%'
AND lower(trim(Sql)) NOT LIKE 'foreign%'
AND lower(trim(Sql)) NOT LIKE 'constraint%'
),
-- Added to make querying a subset easier.
Listing(tbl_name, Ordinal, Name, Constraints)
AS
(
SELECT
tbl_name,
Ordinal,
substr(Clause, 1, instr(Clause, ' ') - 1),
trim(substr(Clause, instr(Clause, ' ') + 1))
FROM
Recordify
WHERE
Ordinal > 0
)
SELECT
tbl_name,
Ordinal,
Name,
Constraints
FROM
Listing
ORDER BY
tbl_name,
lower(Name);
