sqlite3 的系统表 sqlite_master.

http://www.sqlite.org/fileformat2.html

2.5 Storage Of The SQL Database Schema

Page 1 of a database file is the root page of a table b-tree that holds a special table named "sqlite_master" (or "sqlite_temp_master" in the case of a TEMP database) which stores the complete database schema. The structure of the sqlite_master table is as if it had been created using the following SQL:

CREATE TABLE sqlite_master( type text, name text, tbl_name text, rootpage integer, sql text);

The sqlite_master table contains a row for each table, index, view, and trigger in the database schema, except there is no entry for the sqlite_master table itself.

The sqlite_master.type column will be one of the following text strings: 'table', 'index', 'view', or 'trigger' according to the type of object defined. The 'table' string is used for both ordinary and virtual tables.

The sqlite_master.name column will hold the name of the object. For indices that are automatically created by UNIQUE or PRIMARY KEY constraints, the name is "sqlite_autoindex_TABLE_N" where TABLE is replaced by the name of the table that contains the constraint and N is an integer beginning with 1 and increasing by one with each constraint seen.

The sqlite_master.tbl_name column holds the name of a table or view that the object is associated with. For a table or view, the tbl_name column is a copy of the name column. For an index, the tbl_name is the name of the table that is indexed. For a trigger, the tbl_name column stores the name of the table or view that causes the trigger to fire.

The sqlite_master.rootpage column stores the page number of the root b-tree page for tables and indices. For rows that define views, triggers, and virtual tables, the rootpage column is 0 or NULL.

The sqlite_master.sql column stores SQL text that describes the object. This SQL text is a CREATE TABLE, CREATE VIRTUAL TABLE, CREATE INDEX, CREATE VIEW, or CREATE TRIGGER statement that if evaluated against the database file when it is the main database of a database connection would recreated the object.) The text is usually a copy of the original statement used to create the object but with normalizations applied so that the text conforms to the following rules:

The CREATE, TABLE, VIEW, TRIGGER, and INDEX keywords at the beginning of the statement are converted to all upper case letters. The TEMP or TEMPORARY keyword is removed if it occurs after the initial CREATE keyword. Any database name qualifier that occurs prior to the name of the object being created is removed. Leading spaces are removed. All spaces following the first two keywords are converted into a single space.

The text in the sqlite_master.sql column is a copy of the original CREATE statement text that created the object, except normalized as described above and as modified by subsequent ALTER TABLE statements.

For indices that are automatically created by UNIQUE or PRIMARY KEY constraints, the sqlite_master.sql field is NULL.

SQLite数据库中一个特殊的名叫 SQLITE_MASTER 上执行一个SELECT查询以获得所有表的索引。每一个 SQLite数据库都有一个叫 SQLITE_MASTER 的表， 它定义数据库的模式。 SQLITE_MASTER 表看起来如下：

CREATE TABLE sqlite_master (
type TEXT,
name TEXT,
tbl_name TEXT,
rootpage INTEGER,
sql TEXT
);
对于表来说，type 字段永远是 ‘table’，name 字段永远是表的名字。所以，要获得数据库中所有表的列表，使用下列SELECT语句：

SELECT name FROM sqlite_master
WHERE type=’table’
ORDER BY name;
对于索引，type 等于 ‘index’, name 则是索引的名字，tbl_name 是该索引所属的表的名字。不管是表还是索引，sql 字段是原先用 CREATE TABLE 或 CREATE INDEX语句创建它们时的命令文本。对于自动创建的索引（用来实现 PRIMARY KEY 或 UNIQUE约束），sql字段为NULL。

SQLITE_MASTER 表是只读的。不能对它使用 UPDATE、INSERT 或 DELETE。 它会被 CREATETABLE、CREATE INDEX、DROP TABLE 和 DROP INDEX 命令自动更新。

临时表不会出现在 SQLITE_MASTER 表中。临时表及其索引和触发器存放在另外一个叫 SQLITE_TEMP_MASTER的表中。SQLITE_TEMP_MASTER 跟 SQLITE_MASTER 差不多，但它只是对于创建那些临时表的应用可见。如果要获得所有表的列表， 不管是永久的还是临时的，可以使用类似下面的命令：

SELECT name FROM
(SELECT * FROM sqlite_master UNION ALL
SELECT * FROM sqlite_temp_master)
WHERE type=’table’
ORDER BY name