Retrieve list of tables

(1) By tom (younique) on 2021-08-13 09:03:41 [link] [source]

I use SELECT name FROM sqlite_master WHERE type='table' when I need a list of all tables within a database. This list includes internal tables as well, e.g. "sqlite_sequence". The ".tables" CLI command does not.

Is there a more convenient way which does not need any filtering (there seems to be no pragma either)? I'm not sure whether internal tables always start with "sqlite_". How about temporary tables?

If not, may I suggest the beautiful "SHOW TABLES" statement from MySQL. Simple as could be, very helpful, easy to remember, and working not only in CLI but everywhere.

(2) By Harald Hanche-Olsen (hanche) on 2021-08-13 16:52:58 in reply to 1 [source]

I'm not sure whether internal tables always start with "sqlite_".

I am pretty sure I have seen that documented. Moreover, you are not allowed to create a table whose name starts with sqlite_ yourself; you get an error message if you try.

How about temporary tables?

They are listed in temp.sqlite_master.