SQLite Forum

Identifying attached database
Login

Identifying attached database

(1) By oneeyeman on 2020-10-06 23:18:21 [link] [source]

Hi, This question kind of relates to the one I just asked.

Is there a way from the C API to say that there is an attached database?

Thank you.

(2) By Keith Medcalf (kmedcalf) on 2020-10-07 06:16:53 in reply to 1 [link] [source]

There is no C API to directly enumerate the list of "schema" names recognized by a connection.

There is, however, a pragma (SQL Query) to retrieve this information. You may either execute PRAGMA database_list; or SELECT * FROM pragma_database_list; to retrieve information about the schema's available in a connection. (As long as the version of SQLite3 you are using supports this introspection pragma and it has been either enabled or not disabled as is appropriate for that version).

>sqlite
-- Loading resources from C:\Users\KMedcalf/.sqliterc
SQLite version 3.34.0 2020-10-05 19:05:20
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> pragma database_list;
┌─────┬──────┬────────────────────────┐
│ seq │ name │          file          │
├─────┼──────┼────────────────────────┤
│ 0   │ main │                        │
│ 1   │ temp │                        │
│ 2   │ _tz_ │ d:\source\sqlite\tz.db │
└─────┴──────┴────────────────────────┘
sqlite> select * from pragma_database_list;
┌─────┬──────┬────────────────────────┐
│ seq │ name │          file          │
├─────┼──────┼────────────────────────┤
│ 0   │ main │                        │
│ 1   │ temp │                        │
│ 2   │ _tz_ │ d:\source\sqlite\tz.db │
└─────┴──────┴────────────────────────┘
sqlite>

(3) By Keith Medcalf (kmedcalf) on 2020-10-07 06:34:34 in reply to 2 [source]

Note that the return order does not reflect the unqualified table search order.

The query select row_number() over (order by name != 'temp', seq) as search, * from pragma_database_list; will return the results with a "search" column that reflects the unqualified table search order.

>sqlite
-- Loading resources from C:\Users\KMedcalf/.sqliterc
SQLite version 3.34.0 2020-10-05 19:05:20
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> attach 'a.db' as a;
sqlite> attach 'b.db' as b;
sqlite> detach a;
sqlite> attach 'c.db' as c;
sqlite> select row_number() over (order by name != 'temp', seq) as search, * from pragma_database_list;
┌────────┬─────┬──────┬────────────────────────┐
│ search │ seq │ name │          file          │
├────────┼─────┼──────┼────────────────────────┤
│ 1      │ 1   │ temp │                        │
│ 2      │ 0   │ main │                        │
│ 3      │ 2   │ _tz_ │ d:\source\sqlite\tz.db │
│ 4      │ 3   │ b    │ D:\b.db                │
│ 5      │ 4   │ c    │ D:\c.db                │
└────────┴─────┴──────┴────────────────────────┘
sqlite>