In SQLite, the table_list pragma returns information about the tables and views in the schema.
It was first introduced in SQLite version 3.37.0 (released on 2021-11-27).
Syntax
The table_list pragma can be used in any of the following ways:
PRAGMA table_list;
PRAGMA schema.table_list;
PRAGMA table_list(table-name);Where schema is the name of a specific schema for which you’d like to list the tables and views.
And where table-name is the name of specific tables or views that you’d like to list.
Example
PRAGMA table_list;Result:
schema name type ncol wr strict ------ ------------------ ----- ---- -- ------ main sqlite_schema table 5 0 0 temp sqlite_temp_schema table 5 0 0 Store Orders table 2 0 1 Store Customers table 4 1 1 Store Products table 3 0 0 Store Types table 3 0 0 Store Dogs table 3 0 0 Store Cats table 3 0 0 Store vProducts view 3 0 0 Store sqlite_schema table 5 0 0 Pets Events table 4 0 0 Pets Pets table 3 0 0 Pets Types table 2 0 0 Pets Cats table 2 0 0 Pets Dogs table 2 0 0 Pets sqlite_schema table 5 0 0
We can see that the table and view names are listed in the name column. We can also see whether it’s a table or view by looking at the type column. And of course, the schema name is listed in the schema column.
The ncol column contains the number of columns in the table, including generated columns and hidden columns.
The wr column indicates whether or not the table has been defined with the WITHOUT ROWID option. In our example, we can see that the Customers table has been defined with WITHOUT ROWID.
The strict column indicates whether or not the table has been defined with the STRICT option. This option was introduced in SQLite version 3.37.0 (the same version that the table_list pragma was introduced). In our example, we can see that the Customers and Orders tables have been defined with the STRICT option.
Get All Tables in a Specific Database
We can include the schema name in order to return just the tables and views in a given database:
PRAGMA Store.table_list;Result:
schema name type ncol wr strict ------ ------------- ----- ---- -- ------ Store Orders table 2 0 1 Store Customers table 4 1 1 Store Products table 3 0 0 Store Types table 3 0 0 Store Dogs table 3 0 0 Store Cats table 3 0 0 Store vProducts view 3 0 0 Store sqlite_schema table 5 0 0
Get Tables by Name
We can provide the table/view name in order to return all tables/views with that name across all databases:
PRAGMA table_list('Dogs');Result:
schema name type ncol wr strict ------ ---- ----- ---- -- ------ Store Dogs table 3 0 0 Pets Dogs table 2 0 0
In this case we can see that both the Store and Pets schemas have a table called Dogs.