如何在SQLite数据库的表中获取列的列表？

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查询执行此操作的代码。与列相关的元数据的额外奖励积分（例如长度，数据类型等）

7 个解决方案

#1

109

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语句的更多信息，请参阅文档。

#2

Here's the simple way:

这是一个简单的方法：

.schema <table>

#3

just go into your sqlite shell:

进入你的sqlite shell：

$ sqlite3 path/to/db.sqlite3

and then just hit

然后才打

sqlite> .schema

and you will get everything.

你会得到一切。

#4

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.

将显示一个标题，显示输出顶部的所有选定字段（如果您选择SELECT *）。

#5

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
;

#6

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来创建表，这实际上是列的列表。

#7

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是“主要的”

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

#1

109