SQLite架构信息元数据

Question

我需要在SQLite数据库中获取列名和它们的表。我需要的是一个包含两列的结果集：table_name | column_name。

在MySQL中，我可以通过对数据库INFORMATION_SCHEMA执行SQL查询来获得这些信息。但是，SQLite提供了表sqlite_master：

sqlite> create table students (id INTEGER, name TEXT);
sqlite> select * from sqlite_master;
  table|students|students|2|CREATE TABLE students (id INTEGER, name TEXT)

这将导致一个对我没有帮助的动态链接库构造查询(CREATE TABLE)，我需要对其进行解析以获取相关信息。

我需要获取表的列表，并使用列连接它们，或者只获取列和表名列。所以PRAGMA table_info(TABLENAME)不适合我，因为我没有表名。我想要获取数据库中的所有列元数据。

有没有更好的方法通过查询数据库来获取这些信息作为结果集？

Answer 1

你基本上已经在你的问题中命名了解决方案。

要获取表(和视图)的列表，请按如下所示查询sqlite_master

SELECT name, sql FROM sqlite_master
WHERE type='table'
ORDER BY name;

(参见the SQLite FAQ)

要获取有关特定表中列的信息，请按照the SQLite PRAGMA documentation中的说明使用PRAGMA table_info(table-name);。

我不知道有什么方法可以将tablename|columnname作为单个查询的结果返回。我不相信SQLite支持这一点。最好的方法可能是同时使用这两种方法来返回您要查找的信息-首先使用sqlite_master获取表列表，然后使用PRAGMA table_info()遍历它们以获取它们的列。

Answer 2

最新版本的SQLite允许您select against PRAGMA results now，这使得这很容易：

SELECT 
  m.name as table_name, 
  p.name as column_name
FROM 
  sqlite_master AS m
JOIN 
  pragma_table_info(m.name) AS p
ORDER BY 
  m.name, 
  p.cid

其中，p.cid保存CREATE TABLE语句的列顺序，即零索引。

David Garoutte回答了这个here，但是这个SQL应该执行得更快，并且列是按模式排序的，而不是按字母顺序排序的。

请注意，table_info还包含

• type (数据类型，如integer或text)，
• notnull (如果列具有缺省constraint)
• dflt_value，则为1(如果没有缺省键，则为NOT NULL (如果列是表的主键，则为1，否则为0)

实时调频：https://www.sqlite.org/pragma.html#pragma_table_info

Answer 3

有".tables“和".schema table_name”命令，它们为你从"select * from sqlite_master;“得到的结果提供了一种独立的版本。

还有“杂注table_info(table_name);”命令可以获得更好的解析结果，而不是构造查询：

sqlite> .tables
students
sqlite> .schema students
create table students(id INTEGER, name TEXT);
sqlite> pragma table_info(students);
0|id|INTEGER|0||0
1|name|TEXT|0||0

希望，这在某种程度上是有帮助的。