How to list all tables in a Sqlite database?

Question

In Mysql, when I run the command SHOW TABLES I can see all tables present in a given database.

And in the case of Sqlite? How can I do this?

Answer 1

If you are using his "interpreter" you can use the commands .tables or schema, to list tables or their structures.

If you want to do this in code and want a result that can be used for something in your application, refer to the database database dictionary where you have all tables:

SELECT * FROM sqlite_master WHERE type='table';

The WHERE is necessary because there also have the indexes. If you want only some data, you can filter, choose the columns, whatever you want, already know how to use SQL. Just don’t try to modify this and other control tables because there are no triggers that process everything that is needed.

If you test you will see that the structure is saved as text, it puts the command that was used to create the table. It is not very practical to consult, but the information is there.

If you make a ATTACH need to say what database you are referring to. Example:

ATTACH novoBanco.db AS novo;

SELECT * FROM novo.sqlite_master WHERE type='table';

I put in the Github for future reference.

If you need temporary tables you need to consult another place: sqlite_temp_master.

That is in the official FAQ.

One thing few know is that all internal control of databases are usually in normal tables with a special access hardcoded.

Answer 2

Let’s say your db is called MEUDB.

You can run the following command:

SELECT name FROM meudb.sqlite_master WHERE type='table';

Answer 3

1. List the database tables:

   .tables

2. Show table details:

   .schema tablename

3. Show all table records:

   SELECT * FROM tablename;

4. List of all Sqlite commands (terminal/prompt):

   .help

Source

Answer 4

sqlite3 fffffff.sqlite .tables