SQLite 架构信息元数据 [英] SQLite Schema Information Metadata

问题描述

我需要在 SQLite 数据库中获取列名及其表.我需要的是一个有 2 列的结果集: table_name |列名.

I need to get column names and their tables in a SQLite database. What I need is a resultset with 2 columns: table_name | column_name.

在 MySQL 中，我可以通过对数据库 INFORMATION_SCHEMA 的 SQL 查询来获取此信息.但是 SQLite 提供表 sqlite_master:

In MySQL, I'm able to get this information with a SQL query on database INFORMATION_SCHEMA. However the SQLite offers table 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)

这会导致 DDL 构造查询 (CREATE TABLE)，这对我没有帮助，我需要解析它以获取相关信息.

which results a DDL construction query (CREATE TABLE) which is not helpful for me and I need to parse this to get relevant information.

我需要获取表列表并将它们与列连接，或者只获取列和表名列.所以 PRAGMA table_info(TABLENAME) 对我不起作用，因为我没有表名.我想获取数据库中的所有列元数据.

I need to get list of tables and join them with columns or just get columns along with table name column. So PRAGMA table_info(TABLENAME) is not working for me since I don't have table name. I want to get all column metadata in the database.

是否有更好的方法通过查询数据库来获取该信息作为结果集?

Is there a better way to get that information as a result set by querying database?

推荐答案

您基本上已经在问题中命名了解决方案.

You've basically named the solution in your question.

要获取表(和视图)列表，请像在

To get a list of tables (and views), query sqlite_master as in

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

(参见 SQLite 常见问题)

要获取有关特定表中列的信息，请使用 PRAGMA table_info(table-name);，如 SQLite PRAGMA 文档.

To get information about the columns in a specific table, use PRAGMA table_info(table-name); as explained in the SQLite PRAGMA documentation.

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

I don't know of any way to get tablename|columnname returned as the result of a single query. I don't believe SQLite supports this. Your best bet is probably to use the two methods together to return the information you're looking for - first get the list of tables using sqlite_master, then loop through them to get their columns using PRAGMA table_info().

这篇关于SQLite 架构信息元数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！