How to Show Tables in SQLite

Introduction

SQLite is a popular database engine known for its simplicity and efficiency in managing relational databases. It is an embedded SQL database engine, which means it is often used directly within applications, providing a lightweight database solution without the need for a separate server process. In this tutorial, we’ll explore how to view a list of tables in an SQLite database, a common task when you’re getting familiar with the structure of an existing database or debugging.

Prerequisites

You will need to have SQLite installed and accessible. You can interact with SQLite databases through various means, such as the command line, a Python script, or GUI tools like Beekeeper Studio. This guide will use the SQLite command-line tool as it is universally available.

Accessing the SQLite Database

To start, open your terminal or command prompt and access your SQLite database using the SQLite command-line tool. The general command to start interacting with your database is:

sqlite3 your_database_file.db

Replace ‘your_database_file.db’ with the path to your SQLite database file.

Listing Tables Using the .tables command

SQLite offers the .tables command as an easy-to-use method to list all available tables in your active database.

From your SQLite command-line shell, input the following:

.tables

By executing this command, SQLite will return a list of all table names within your current database.

Expected Output: (using the ‘sakila.db’ sample database)

actor                   experiments             payment
address                 film                    rental
category                film_actor              sales_by_film_category
city                    film_category           sales_by_store
country                 film_list               staff
customer                film_text               staff_list
customer_list           inventory               store
events                  language

How does the .tables command work?

SQLite instructs the database to return the names of all tables and views in the main and attached databases. It does this by executing a SELECT command on the sqlite_master table under the hood.

Querying the sqlite_master Table

For more control, you can query the ‘sqlite_master’ table directly, which stores the metadata of the database, including table definitions.

For example:

SELECT name FROM sqlite_master WHERE type='table';

This SQL command selects all the records in the name column of the sqlite_master table where the type is ‘table’.

Expected Output: (using the ‘sakila.db’ sample database)

---------------
actor
sqlite_sequence
country
city
address
language
category
customer
film
film_actor
film_category
film_text
inventory
staff
store
payment
rental
experiments
events

Show Table Schema Using the.schema Command

To examine the structure or ‘schema’ of your table (i.e., the table columns and their respective data types), SQLite provides the .schema command:

.schema your_table_name

Expected Output: (using the ‘actor’ table in the ‘sakila.db’ sample database)

CREATE TABLE actor (
  actor_id integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  first_name VARCHAR(45) NOT NULL,
  last_name VARCHAR(45) NOT NULL,
  last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
  , "email_address" VARCHAR(255) NULL);
CREATE INDEX idx_actor_last_name ON actor(last_name)
;
CREATE TRIGGER actor_trigger_ai AFTER INSERT ON actor
 BEGIN
  UPDATE actor SET last_update = DATETIME('NOW')  WHERE rowid = new.rowid;
 END;
CREATE TRIGGER actor_trigger_au AFTER UPDATE ON actor
 BEGIN
  UPDATE actor SET last_update = DATETIME('NOW')  WHERE rowid = new.rowid;
 END;

Replace ‘your_table_name’ with the name of your table.

This command will return the CREATE TABLE statement used to create the table in your database.

Using a SELECT sql Statement

Alternatively, you can fetch the schema using a SELECT statement on the sqlite_master table:

SELECT sql FROM sqlite_master WHERE name = 'your_table_name';

This command will return the same CREATE TABLE statement as the .schema command without the ‘INDEX’ and ‘TRIGGER’ statements.

Using the PRAGMA Command to Get Table Info

Another approach in SQLite is to use the PRAGMA command, which provides different characteristics of the SQLite database. One useful PRAGMA command is ‘table_info’, which can be used to get information about the columns in a table.

While ‘table_info’ does not list all the tables, it can be used alongside table listing commands to understand table structures immediately after finding their names.

Example:

PRAGMA table_info('actor');

Expected Output: (using the ‘actor’ table in the ‘sakila.db’ sample database)

cid  name           type          notnull  dflt_value         pk
---  -------------  ------------  -------  -----------------  --
0    actor_id       INTEGER       1                           1
1    first_name     VARCHAR(45)   1                           0
2    last_name      VARCHAR(45)   1                           0
3    last_update    TIMESTAMP     1        CURRENT_TIMESTAMP  0
4    email_address  VARCHAR(255)  0                           0

Each row represents a column in the ‘actor’ table, with the fields showing the column’s ‘cid’, ‘name’, ‘type’, it’s ‘not null’ status, its ‘default value’, and ‘primary key’ informatiion, respectively.

Conclusion

Listing tables in SQLite is a fundamental skill for database management and inspection. Whether you use the .tables command for a quick list, query the sqlite_master table for a more programmatic approach, or use PRAGMA statements to gather detailed information about table structures, SQLite offers a variety of tools to help you understand and interact with your database effectively.

While understanding and writing SQL commands to view your SQLite tables can be rewarding, it can also be time-consuming and complex, especially when dealing with vast databases.

Beekeeper Studio provides a user-friendly interface to the tables in your database making navigation through numerous tables more convenient.

Moreover, to fetch the schema of any table, just click on the table name, and the schema will appear in a well-structured format.