SQLite sqlite_schema missing

[_Flaviu]

How can I get the number of the tables from a sqlite3 database ? I downloaded from here: SQLite Sample Database And Its Diagram (in PDF format)[^] chinook.db But when I try to get all tables from this database:

SELECT * FROM sqlite_schema WHERE type = 'table' AND name NOT LIKE 'sqlite_%'

I got:

no such table: sqlite_schema

I also tried to create a database on my own:

SQLite::Database db("C:\\Temp\\mydb.bd3", SQLite::OPEN_READWRITE | SQLite::OPEN_CREATE);
db.exec(_T("CREATE TABLE sqlite_schema(type TEXT, name TEXT, tbl_name TEXT, rootpage INTEGER, sql TEXT)"));
db.exec(_T("PRAGMA foreign_keys = ON"));

and

db.exec(_T("DROP TABLE IF EXISTS master"));
db.exec(_T("CREATE TABLE master(masterid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, name TEXT)"));
db.exec(_T("DROP TABLE IF EXISTS app"));
db.exec(_T("CREATE TABLE app (appid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, name TEXT)"));
db.exec(_T("DROP TABLE IF EXISTS device"));
db.exec(_T("CREATE TABLE device (deviceid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, masterid INTEGER, appid INTEGER, name TEXT, FOREIGN KEY(masterid) REFERENCES master(masterid), FOREIGN KEY(appid) REFERENCES app(appid))"));

but when I try the same SQL:

SELECT * FROM sqlite_schema WHERE type = 'table' AND name NOT LIKE 'sqlite_%'

I got the same error:

no such table: sqlite_schema

How can I assure to retrieve all tables from a db3 (db) file ?

[Richard Deeming]

SQLite Forum: Documentation issue (wrong table name) in FAQ, point 7[^]

The name has been changed to "sqlite_schema" as of version 3.33.0. The older name "sqlite_master" is still accepted as an alias for backwards compatibility. The FAQ is for version 3.33.0. You must be using an older version of SQLite.

Assuming you're not using 3.33.0 or later, try using the sqlite_master table instead.

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[_Flaviu]

Thanks a lot. That was the issue. I have a small request: can you share which structure has this table, sqlite_master ? I need a database_id, table_id, or appropriate.

[_Flaviu]

Ahh, I think I have found it: SQLite: sqlite_master (internal schema object)[^]