SQLite doesn’t have a SHOW TABLES statement like MySQL and MariaDB have, but it does have a similar command.

In SQLite, you can use the .tables command to show a list of tables. You can alternatively use the table_list pragma to do the job.

The .tables Command

In SQLite, the .tables command is probably the closest thing to MySQL and MariaDB’s SHOW TABLES statement. Here’s an example:

.tables

Result:

Customers  Employees  Students   Teachers

Here, four tables are returned.

The .tables command queries the sqlite_schema table for all attached databases. It returns both tables and views. If you want to exclude views, you can query the sqlite_schema table directly. See 2 Ways to List the Tables in an SQLite Database for an example of this.

Or if tables and/or views have a consistent naming convention (such as a prefix), then you could include/exclude them by appending a pattern after the .tables part.

Here’s an example with a pattern:

.tables t%

Result:

Teachers

In this case, I used t% as the pattern, which resulted in all tables that start with the letter t to be returned.

The table_list Pragma

Another potential SHOW TABLES equivalent in SQLite is the table_list pragma:

PRAGMA table_list;

Result:

schema  name                type   ncol  wr  strict
------  ------------------  -----  ----  --  ------
main    Customers           table  2     0   0     
main    Employees           table  2     0   0     
main    Students            table  2     0   0     
main    Teachers            table  2     0   0     
main    sqlite_schema       table  5     0   0     
temp    sqlite_temp_schema  table  5     0   0     

This returns more information about each table than the .tables command. You can also narrow it down to just a given database or even a table name.

Here’s an example of specifying the database:

PRAGMA main.table_list;

Result:

schema  name           type   ncol  wr  strict
------  -------------  -----  ----  --  ------
main    Customers      table  2     0   0     
main    Employees      table  2     0   0     
main    Students       table  2     0   0     
main    Teachers       table  2     0   0     
main    sqlite_schema  table  5     0   0     

And a given table:

PRAGMA main.table_list(Customers);

Result:

schema  name       type   ncol  wr  strict
------  ---------  -----  ----  --  ------
main    Customers  table  2     0   0