如何枚举sqlite中的所有表及其字段？？

[jackzhhuang]

我有一份sqlite数据库文件，但不知道里面的表名，如何枚举出所有的表名以及其字段名？？

关键是sqlite的sql语句如何写？我试了一些sql语句不行，特来求助。

[ACMAIN_CHM]

sqlite> .tables
MailLabel numtable t1 test
sqlite>

sqlite> .help
.backup ?DB? FILE      Backup DB (default "main") to FILE
.bail ON|OFF           Stop after hitting an error.  Default OFF
.databases             List names and files of attached databases
.dump ?TABLE? ...      Dump the database in an SQL text format
.echo ON|OFF           Turn command echo on or off
.exit                  Exit this program
.explain ON|OFF        Turn output mode suitable for EXPLAIN on or off.
.genfkey ?OPTIONS?     Options are:
                         --no-drop: Do not drop old fkey triggers.
                         --ignore-errors: Ignore tables with fkey errors
                         --exec: Execute generated SQL immediately
                       See file tool/genfkey.README in the source
                       distribution for further information.
.header(s) ON|OFF      Turn display of headers on or off
.help                  Show this message
.import FILE TABLE     Import data from FILE into TABLE
.indices TABLE         Show names of all indices on TABLE
.load FILE ?ENTRY?     Load an extension library
.mode MODE ?TABLE?     Set output mode where MODE is one of:
                         csv      Comma-separated values
                         column   Left-aligned columns.  (See .width)
                         html     HTML <table> code
                         insert   SQL insert statements for TABLE
                         line     One value per line
                         list     Values delimited by .separator string
                         tabs     Tab-separated values
                         tcl      TCL list elements
.nullvalue STRING      Print STRING in place of NULL values
.output FILENAME       Send output to FILENAME
.output stdout         Send output to the screen
.prompt MAIN CONTINUE  Replace the standard prompts
.quit                  Exit this program
.read FILENAME         Execute SQL in FILENAME
.restore ?DB? FILE     Restore content of DB (default "main") from FILE
.schema ?TABLE?        Show the CREATE statements
.separator STRING      Change separator used by output mode and .import
.show                  Show the current values for various settings
.tables ?PATTERN?      List names of tables matching a LIKE pattern
.timeout MS            Try opening locked tables for MS milliseconds
.width NUM NUM ...     Set column widths for "column" mode
sqlite>

[ACMAIN_CHM]

如果在程序中你可以通过 sqlite_master 表得到所有表的信息。

sqlite> select type, name, tbl_name from sqlite_master order by type;
type|name|tbl_name
index|sqlite_autoindex_t1_1|t1
index|sqlite_autoindex_numtable_1|numtable
table|t1|t1
table|numtable|numtable
table|test|test
table|sqlite_stat1|sqlite_stat1
table|MailLabel|MailLabel
sqlite>

sqlite_master 结构如下
SQLite Master Table Schema
-----------------------------------------------------------------
Name Description
-----------------------------------------------------------------
type The object’s type (table, index, view, trigger)
name The object’s name
tbl_name The table the object is associated with
rootpage The object’s root page index in the database (where it begins)
sql The object’s SQL definition (DDL)

[ACMAIN_CHM]

字段名　则无法象表名一样从数据库系统表中直接获得。你只能打开每个表后，遍历其字段。比如使用ADO.recordset打开表后,通过fields.name 得到列名和其它信息（字段定义类型，长度）

或者 在sqlite 中用 .

.schema ?TABLE? Show the CREATE statements

显示表结构

sqlite> .schema t1
CREATE TABLE t1 (id int primary key, col1 int,col2 int);
sqlite>
sqlite> select sql from sqlite_master where name='t1' and type='table';
sql
CREATE TABLE t1 (id int primary key, col1 int,col2 int)
sqlite>
sqlite> select * from t1;
id|col1|col2
1|1|1
2|2|2
3|3|3

[wwwwb]

在SQLITE中可以通过
select * from sqlite_master
得到所有表名及建表SQL

如果在语言环境中可以通过ADO、ADOX得到所有字段名、表名

[wwwwb]

vba示例：
Dim cn As New ADODB.Connection
Dim ff1 As New ADOX.Catalog
Dim ff2 As New ADOX.Table
Dim ff3 As New ADOX.Column
ff = "DRIVER={SQLite3 ODBC Driver};Database=j:\temp\dd"
cn.Open ff
Set ff1.ActiveConnection = cn
For Each ff2 In ff1.Tables
If ff2.Type = "TABLE" Then
For Each ff3 In ff2.Columns
MsgBox ff2.Name & ff3.Name & ff3.Type

Next
End If

Next

[WWWWA]

在SQLITE中没有办法直接得到字段名，在系统表中有表名及建表的SQL语句，通过
解析SQL语句来得到字段名， OR 用语言来取得

[alun1279]

PRAGMA table_info(表名) 可以得到字段信息

[austo]

[Quote=引用 5 楼 wwwwb 的回复:]
vba示例：
Dim cn As New ADODB.Connection
Dim ff1 As New ADOX.Catalog
Dim ff2 As New ADOX.Table
Dim ff3 As New ADOX.Column
ff = "DRIVER={SQLite3 ODBC Driver};Database=j:\temp\dd"
cn.Open ff
Set ff1.ActiveConnection = cn
For Each ff2 In ff1.Tables
If ff2.Type = "TABLE" Then
  For Each ff3 In ff2.Columns
  MsgBox ff2.Name & ff3.Name & ff3.Type

  Next
End If

Next

[/Quote]
这个还不太懂，呵呵

[横云断岭]

"select count(*) from sqlite_master where type='table' and name='%s'"

[横云断岭]

select * from sqlite_master;

[「已注销」]

sqlite3_stmt *stmt = NULL;
const char *tail=NULL;
int ret_code = sqlite3_prepare_v2(m_db, "select * from %s where 0=1", -1, &stmt, &tail);
if(ret_code!=SQLITE_OK)
{
if(stmt)
sqlite3_finalize(stmt);
return
}

int col_count = sqlite3_column_count(stmt);

--------------------------------遍历所有的col----------------------
int coldatatype = sqlite3_column_type(m_stmt, col);

const char* colname = sqlite3_column_name(m_stmt, col);
if(colname && strlen(colname))
......; //列名
int returnType = 0;
switch(coldatatype)
{
case SQLITE_TEXT:
...;
break;
case SQLITE_INTEGER:
...;
break;
case SQLITE_FLOAT:
...;
break;
case 12:
...;
break;
case SQLITE_BLOB:
...;
break;
case SQLITE_NULL:
returnType = 0;
break;
default:
returnType = -1;
break;
} //填列的数据类型


if (returnType <= 0)
{
const char* dectype = sqlite3_column_decltype(m_stmt, col);
if( NULL == dectype || 0 == strlen(dectype) )
...;
returnType = col_type_from_name(dectype);
if(returnType==0)
{
...;
}
}

int col_size = sqlite3_column_bytes(m_stmt, col);
--------------------------------遍历过程结束----------------


if(stmt)
sqlite3_finalize(stmt);

return;