SQLite show tables makes it easy to get an overview of your database layout and dig into each table’s details. This comprehensive guide will explore the various techniques for viewing table schemas in SQLite databases.
Also read: SQLite Create Table
What's in this article
SQLite Show Tables Commands
The main commands for showing tables and schemas in SQLite are:
|List names of all tables and views in the database|
|Show tables matching a specified pattern|
|Display CREATE statement used to generate a table|
|Show detailed information on a table’s columns|
Let’s look at each of these in more depth, along with examples.
List All Tables using SQLite Show Tables .tables
The most straightforward to use SQLite Show Tables to display tables from a database is using the
.tables command with no arguments:
This will output a list of all table and view names in the current database:
users posts comments user_profiles post_tags
.tables shows both tables and views. To only show tables, you can use
.tables sqlite_master which will filter system views.
You can also filter the list only to show tables matching a specified pattern using
This example would match all tables starting with “post”. The
% wildcard allows matching any number of trailing characters.
View CREATE Statement with SQLite Show Tables .schema
.tables shows the names of tables. To see the schema of a table, you need to use the
.schema command. This will display the
CREATE TABLE statement used to generate the table:
CREATE TABLE posts ( id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL, title TEXT, body TEXT, publish_date DATE NOT NULL );
This provides useful insight into the columns, data types, constraints, and indexes that make up a table.
You can specify any table name after
.schema to view its definition.
Get Column Details with PRAGMA table_info()
For even more detailed information on a table’s columns, you can query the
sqlite_master database using
This will return rows containing the column name, data type, whether NULL is allowed, and other properties of each column in a table:
cid|name|type|notnull|dflt_value|pk 0|id|INTEGER|1||1 1|user_id|INTEGER|1||0 2|title|TEXT|0||0 3|body|TEXT|0||0 4|publish_date|DATE|1||0
Some key pieces of info here:
cid– Column index starting at 0
name– Column name
type– Data type (TEXT, INTEGER, DATE, etc)
notnull– 1 if column has a NOT NULL constraint
pk– 1 if column is part of the primary key
This provides lower-level details that
.schema does not show directly.
Open a Connection to an SQLite Database
To use these SQLite show table commands, you first need to open a connection to a database using the
sqlite3 command line program:
$ sqlite3 database.db SQLite version 22.214.171.124 2014-08-15 11:53:05 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite>
This will start the interactive shell and allow you to enter commands.
Alternatively, you can execute SQL statements directly using
$ sqlite3 database.db ".tables" posts users comments
This opens the database, runs the
.tables command, and exits.
Also read: SQLite Commands – A Comprehensive Guide
Examples of SQLite Show Tables
Let’s look at examples of using these SQLite Show Tables commands in a sample database.
First, create a simple database with two tables using SQLite statements:
CREATE TABLE users ( id INTEGER PRIMARY KEY, name TEXT NOT NULL ); CREATE TABLE posts ( id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL, title TEXT NOT NULL, body TEXT NOT NULL );
Now connect to this database and use
.tables to view the tables:
sqlite> .tables users posts
Check the schema for the posts table:
sqlite> .schema posts CREATE TABLE posts ( id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL, title TEXT NOT NULL, body TEXT NOT NULL );
And get column details using
sqlite> PRAGMA table_info(posts); 0|id|INTEGER|1||1 1|user_id|INTEGER|1||0 2|title|TEXT|1||0 3|body|TEXT|1||0
We can see the column names, types, and constraints for the posts table.
Benefits of Exploring Schemas with SQLite Show Tables
There are a few key benefits to taking the time to explore table schemas using SQLite Show Tables:
- Understand the database structure – By seeing which tables exist and their columns/types, you better understand how the database is organized.
- Debug issues – If queries are not working as expected, comparing the actual schema vs. your assumptions can uncover issues.
- Optimize queries – Knowing which columns are indexed can help write faster queries by filtering on indexed columns.
- Ensure stability – Reviewing schemas after big changes helps ensure alter table statements are executed correctly.
- Document the database – The schema often serves as documentation if original DB creation scripts are unavailable.
- Identify bottlenecks – Examining column types and constraints may reveal bottlenecks that can be optimized.
Using SQLite’s schema introspection capabilities makes database development and maintenance much easier.
SQLite Show Tables GUI Tools
In addition to the command line shell, many graphical SQLite tools provide easy ways to view table schemas visually:
- DB Browser for SQLite – Open source GUI for managing SQLite databases. Shows a list of tables along with their columns, types, and other properties.
- SQLite Studio – Commercial multi-platform SQLite GUI. Includes a database explorer pane for browsing tables.
- SQLite Expert – Commercial Windows program for managing SQLite databases. Features a schema viewer and visual query builder.
- DBeaver – Open source universal database tool that supports SQLite, among many others. Provides schema explorer and SQL editor.
These tools can supplement the command line when you need a more graphical interface for exploring and managing SQLite databases.
Advanced Techniques for Using SQLite Show Tables
In addition to the basic commands we’ve covered, there are some more advanced techniques for querying and understanding SQLite database schemas:
List attached databases
SQLite allows attaching multiple databases to a single connection which can be queried as one. You can use
PRAGMA database_list to see all attached databases:
This will list the file path for each attached database. You can then query tables across all attached databases.
Generate CREATE table statement
.schema TABLE_NAME shows the CREATE statement for an existing table, you can also generate a CREATE statement for a table using:
SELECT sql FROM sqlite_master
WHERE type='table' AND name='table_name';
This extracts the SQL used to create the table from the sqlite_master catalog.
Export schema as SQL
To export the entire schema for a database as SQL create statements, you can query the sqlite_master table and output the SQL:
SELECT sql FROM sqlite_master;
This will output all CREATE statements for tables and other objects in the database. You can save the output to a SQL file to port the schema to another database.
Query column metadata
SQLite stores additional metadata on columns in a separate table called
pragma_table_info(). You can join this table to see metadata directly tied to each column:
SELECT m.name AS table_name, i.name AS column_name, i.type AS data_type, i.notnull AS not_null FROM sqlite_master AS m JOIN pragma_table_info(m.name) AS i;
This provides an alternative way to query schema data that requires explicitly joining the metadata table.
SQLite offers several simple but powerful commands to show tables and view database schemas. Using
PRAGMA table_info() provides great insight into your database structure and aids in development and debugging.
Understanding how to access table definitions programmatically is an indispensable skill for any SQLite developer. Whether you use the interactive shell, command line invocation, or GUI tools, being able to explore and query table schemas will improve your ability to work with SQLite databases.