Show Tables Using sqlite_master Table

sqlite_master table is the system table in SQLite that is used to store the metadata of the database. This table contains various data related to the database, such as tables, indices, views and more.

Below image shows how to see all tables in a database using sqlite_master table :

Explanation:

In the above image we have performed the same steps for opening the database file as discussed above, but this time we have written a query to extract the tables from database file.

The output of query has shown many columns, let’s discuss about each column in brief

• type -> It shows the type of database object such as table, indices, views etc.
• name -> It shows the name of database object.
• tbl_name -> It shows the name of the table of which that database object is, In case of type=’table‘ name and tbl_name are same.
• rootpage -> In SQLite each table is organized as a B-Tree structure. So the rootpage value points to the page number of root B-Tree page of table in the database file.
• sql -> It shows the sql statement which was used to create that particular table.

Now in the above image we can clearly see that the query has given the three outputs for the query : select * from sqlite_master where type=’table’;

Here this query has given the 3 tables : student, sqlite_sequence, teacher

student and teacher tables are created by the user, but sqlite_sequence is created by the SQLite to know the maximum row id for the auto incrementing columns for all the tables in database file. This is required so that SQLite can add the row id to the new row with the help of this table.

SQLite is a lightweight database library written in C which is used for embedding the database with applications. SQLite is a serverless, lightweight, cross-platform, and highly reliable database engine that provides the standard SQL syntax making it easy to use standalone or integrate with any other programming language. SQLite is used to develop embedded applications and is widely used in small-scale and medium-scale databases.

In this article, we will understand how to show tables of databases using various methods and so on. After reading this article you will have decent knowledge about how to SHOW Tables in SQLite.