list 如何获取 SQLite 数据库表中的列列表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/604939/
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
How can I get the list of a columns in a table for a SQLite database?
提问by AngryHacker
I am looking to retrieve a list of columns in a table. The database is the latest release of SQLite (3.6, I believe). I am looking for code that does this with a SQL query. Extra bonus points for metadata related to the columns (e.g. length, data type, etc...)
我希望检索表中的列列表。该数据库是 SQLite 的最新版本（我相信是 3.6）。我正在寻找使用 SQL 查询执行此操作的代码。与列相关的元数据的额外加分（例如长度、数据类型等...）
回答by Bryan Kyle
What you're looking for is called the data dictionary. In sqlite a list of all tables can be found by querying sqlite_master table (or view?)
您要查找的内容称为数据字典。在 sqlite 中，可以通过查询 sqlite_master 表（或视图？）找到所有表的列表。
sqlite> create table people (first_name varchar, last_name varchar, email_address varchar);
sqlite> select * from sqlite_master;
table|people|people|2|CREATE TABLE people (first_name varchar, last_name varchar, email_address varchar)
To get column information you can use the pragma table_info(table_name)statement:
要获取列信息，您可以使用以下pragma table_info(table_name)语句：
sqlite> pragma table_info(people);
0|first_name|varchar|0||0
1|last_name|varchar|0||0
2|email_address|varchar|0||0
For more information on the pragma statements, see the documentation.
有关 pragma 语句的更多信息，请参阅文档。
回答by ifightcrime
Here's the simple way:
这是简单的方法：
.schema <table>
回答by inVader
The question is old but the following hasn't been mentioned yet.
这个问题很老了，但还没有提到以下内容。
Another convenient way in many cases is to turn headers on by:
在许多情况下，另一种方便的方法是通过以下方式打开标题：
sqlite> .headers on
Then,
然后，
sqlite> SELECT ... FROM table
will display a headline showing all selected fields (all if you SELECT *) at the top of the output.
将在输出顶部显示一个标题，显示所有选定的字段（如果您选择 *，则全部）。
回答by Majd Taby
just go into your sqlite shell:
只需进入您的sqlite shell：
$ sqlite3 path/to/db.sqlite3
and then just hit
然后就打
sqlite> .schema
and you will get everything.
你会得到一切。
回答by David Garoutte
Here's a SELECT statement that lists all tables and columns in the current database:
这是一个 SELECT 语句，它列出了当前数据库中的所有表和列：
SELECT m.name as tableName,
p.name as columnName
FROM sqlite_master m
left outer join pragma_table_info((m.name)) p
on m.name <> p.name
order by tableName, columnName
;
回答by lewdev
This is a query that lists all tables with their columns, and all the metadata I could get about each column as OP requested (as bonus points).
这是一个查询，列出了所有表及其列，以及我可以根据 OP 请求（作为奖励积分）获得的关于每一列的所有元数据。
SELECT
m.name AS table_name,
p.cid AS col_id,
p.name AS col_name,
p.type AS col_type,
p.pk AS col_is_pk,
p.dflt_value AS col_default_val,
p.[notnull] AS col_is_not_null
FROM sqlite_master m
LEFT OUTER JOIN pragma_table_info((m.name)) p
ON m.name <> p.name
WHERE m.type = 'table'
ORDER BY table_name, col_id
Thanks to @David Garoutte for showing me how to get pragma_table_infoto work in a query.
感谢@David Garoutte 向我展示了如何开始pragma_table_info处理查询。
Run this query to see all the table metadata:
运行此查询以查看所有表元数据：
SELECT * FROM sqlite_master WHERE type = 'table'
回答by some ideas
Building on the above, you can do it all at once:
在上述基础上，您可以一次完成所有操作：
sqlite3 yourdb.db ".schema"
That will give you the SQL to create the table, which is effectively a list of the columns.
这将为您提供创建表的 SQL，该表实际上是一个列列表。
回答by Choca Croc
I know, it's been a long time but it's never too late… I had a similar question with TCL as interpreter and after several search, found nothing good for me. So I propose something based on PRAGMA, knowing that your DB is “main”
我知道，这已经很长时间了，但永远不会太晚......我有一个与 TCL 作为解释器类似的问题，经过多次搜索，没有发现对我有好处。所以我提出了一些基于 PRAGMA 的东西，知道你的数据库是“主要的”
db eval { PRAGMA main.table_info(<your table name>) } TBL { puts $TBL(name) }
And array use to obtain a list
和数组用于获取列表
set col_list {}
db eval { PRAGMA main.table_info(<your table name>) } TBL { lappend col_list $TBL(name) }
puts $col_list
