How to list the tables in a SQLite database file t

Question

What SQL can be used to list the tables, and the rows within those tables in a SQLite database file - once I have attached it with the ATTACH command on the SQLite 3 command line tool??

Answer 1

There is a command available for this on the SQLite command line:

.tables ?PATTERN?      List names of tables matching a LIKE pattern

Which converts to the following SQL:

SELECT name FROM sqlite_master
WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%'
UNION ALL
SELECT name FROM sqlite_temp_master
WHERE type IN ('table','view')
ORDER BY 1

Answer 2

.da to see all databases - one called 'main'

tables of this database can be seen by

SELECT distinct tbl_name from sqlite_master order by 1;

The attached databases need prefixes you chose with AS in the statement ATTACH e.g. aa (, bb, cc...) so:

SELECT distinct tbl_name from aa.sqlite_master order by 1;

Note that here you get the views as well. To exclude these add where type = 'table' before ' order'

Answer 3

According to the documentation, the equivalent of MySQLs' SHOW TABLES; is:

The ".tables" command is similar to setting list mode then executing the following query:

SELECT name FROM sqlite_master
  WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%'
UNION ALL
SELECT name FROM sqlite_temp_master
  WHERE type IN ('table','view')
ORDER BY 1;

However, if you are checking if a single table exists (or to get its details), see @LuizGeron answer.

Answer 4

Use:

import sqlite3

TABLE_LIST_QUERY = "SELECT * FROM sqlite_master where type='table'"

Answer 5

Since nobody has mentioned about the official reference of SQLite, I think it may be useful to refer to it under this heading:

https://www.sqlite.org/cli.html

You can manipulate your database using the commands described in this link. Besides, if you are using Windows OS and do not know where the command shell is, that is in the SQLite's site:

https://www.sqlite.org/download.html

After downloading it, click sqlite3.exe file to initialize the SQLite command shell. When it is initialized, by default this SQLite session is using an in-memory database, not a file on disk, and so all changes will be lost when the session exits. To use a persistent disk file as the database, enter the ".open ex1.db" command immediately after the terminal window starts up.

The example above causes the database file named "ex1.db" to be opened and used, and created if it does not previously exist. You might want to use a full pathname to ensure that the file is in the directory that you think it is in. Use forward-slashes as the directory separator character. In other words use "c:/work/ex1.db", not "c:\work\ex1.db".

To see all tables in the database you have previously chosen, type the command .tables as it is said in the above link.

If you work in Windows, I think it might be useful to move this sqlite.exe file to same folder with the other Python files. In this way, the Python file writes to and the SQLite shell reads from .db files are in the same path.