List Attached Databases using a SELECT command in SQLite

Question

Is there a SELECT command that can list all attached databases similar to the .database command available in sqlite3?

Answer 1

You cannot do this with a SELECT statement that I know of (though you might want to look around in the main database, this data might be stored there). However, there is a solution. If you execute the following statement it will return the databases attached for the current connection:

PRAGMA database_list;

The first row will always be the main database, the second will always be the temp database. Any further databases are after these first two. You can run this statement against your database the same way you would a SELECT statement from your code in c# (or anything else for that matter).

Here is a good reference:

SQLite PRAGMA statement reference

Good luck!

Answer 2

The accepted answer was correct when it was posted, but in SQLite 3.16.0 and later most of the side-effect free pragmas can also be accessed as so called pragma functions.

Which means you can write:

sqlite> .headers on
sqlite> select * from pragma_database_list;
seq|name|file
0|main|
2|a|D:\a.sqlite
3|b|D:\b.sqlite
4|c|D:\c.sqlite

.headers on is completely optional, but very useful, since the column names these pragma functions return are not documented anywhere.

Be aware though, part of the reason they are undocumented is 'This feature is experimental and is subject to change'.