In this short tutorial, we will cover various methods and techniques to show database tales in an SQLite database.
Let us jump in and learn more.
Importing Sample Database
Before we proceed and discuss how to show tables in an SQLite database, let us prepare a sample database.
For this tutorial, we will use the sakila database. The link is provided below:
https://github.com/bradleygrant/sakila-sqlite3
You can also download the database file with wget as shown:
Once downloaded, load the database with the command:
The command above should import the sakila database in the SQLite interactive shell.
SQLite Show Database Tables – Method 1
The first and most common method we can use to show the tables in a given SQLite database is the .tables command.
This command will list all the tables of the selected database as shown:
The following output shows the tables in the sakila database:
Keep in mind that you can use the .table, .ta, and .tables commands to show the tables in a given database. All commands are aliases for listing the tables of a database.
To search for a table matching a specific pattern, you can use the syntax shown:
The command will perform a search similar to the SQLite LIKE operator.
An example is shown below:
The command above should return the tables with the name starting with the film. An example output is as shown:
You can check our tutorial on the SQLite LIKE operator to learn more about various patterns and wildcard characters you can use.
SQLite Show Tables – Method 2
As you can guess, SQLite is a relational database that provides the familiar SQL syntax. We can use a SQL query to show the tables of a given database.
The sqlite_schema table stores information such as tables of a given database.
An example query is shown below:
The query above should select the values where the type is a table, and the name does not follow the sqlite_ pattern. This removes system tables from the result.
The resulting output is as shown:
actor
country
city
address
language
And there you have it, a way of using SQL commands to list tables of a given SQLite database.
Conclusion
In this tutorial, you learned how to use SQLite command and SQL query to show all the tables of a given database.
Thanks for reading!!
