How to list the Tables in a SQLite Database File ?

SQLite is a database engine which is written in C programming language. SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world.

The source code for SQLite is in the public domain. SQLite engine is not a standalone process like other databases, you can link it statically or dynamically as per your requirement with your application. SQLite accesses its storage files directly. In this article, we'll learn how to list all the tables in a SQLite database file that was opened with an ATTACH statement. Before moving into the topic some prerequisites required are

Prerequisites

Before moving into the topic, some prerequisites are required:

• Installation of SQLite Database
• Creating databases and tables in SQLite
• Creating database files in SQLite

Installation of SQLite Database

After installation of the SQLite database engine, create a folder in your desired directory. Now using the command prompt navigate to the directory where you have created the folder and use the below command to create a SQLite database file.

sqlite>  sqlite3 database_name.db;

After the creation of the database, we can access it using the below command.

sqlite>  .databases

Example for creation and access of database file named 'geeks.db'

Creating Tables in SQLite

Similar to other databases, we use same syntax in SQLite. The command used to create a table is,

sqlite>  create table table_name( column_name datatype(size) constraint);

After creating a table we can access it or retrieve it by using the below command,

sqlite>  .tables

Example for creation of table named geek1 with geek_id of integer as primary key.

As we have seen above how to create databases in SQLite and creation of tables in SQLite databases, now we will look how to connect a SQLite database file using other method, i.e., using ATTACH statement.

ATTACH -SQLite

SQLite ATTACH or ATTACH DATABASE statement is used to select or connect to a particular database, and after this command, all SQLite statements will be executed under the attached database. Also ATTACH statement allows us to connect to another database file to our current database connection and execute commands under attached database using its alias name. Hence, this is helpful in working with multiple databases simultaneously.

Connecting SQLite database file using ATTACH statement

In SQLite database files can be opened or connected in two ways

• using 'sqlite3 database_name.db'
  
• using 'ATTACH DATABASE' statement

Here are the steps to list all the tables in a SQLite database file opened with ATTACH DATABASE statement.

1. Install SQLite into the system if it is not installed.
2. Create a database and create multiple tables in it which are to be listed.
3. Now close the connection of SQLite database engine and then open or connect the database file using 'ATTACH DATABASE' statement.
4. Now list the tables present in the database file using the syntax followed.

Now let us look into how to connect a database in SQLite using ATTACH statement.

Opening a Database File with ATTACH or ATTACH DATABASE Statement

There are two ways through which we can open or connect to a database file as follows:

1. Using ATTACH statement to open or connect to a database file:

The command used is,

sqlite>  ATTACH 'database_name.db' AS 'alias_name';

2. We can use ATTACH DATABASE statement to open or connect to a database file:

The command used is,

sqlite>  ATTACH DATABASE 'database_name.db' AS 'alias_name';

Listing Tables of Database Opened with ATTACH Statement

1. Command used to show all the tables in the attached database file:

sqlite>  SELECT name FROM alias_name.sqlite_master WHERE type='table';

2. To show temporary tables in the database file, we need to run this:

sqlite>  SELECT name FROM sqlite_temp_master WHERE type='table';

Example of Listing All the Tables in attached Database File Named 'geeks.db',

Before listing tables in a database file, We will create a database named geeks.db and create multiple tables in it. Command used to create database named geeks.db is,

sqlite3 geeks.db

Now let us create multiple tables geek1, geek2, geek3, geek4 in the above database which is created,

Now we will commit and close the connection and connect again using ATTACH statement. Command used to attach database file which is created above i.e., geeks.db is,

sqlite>  ATTACH 'geeks.db' as 'geek_db';

After attaching database file geeks.db we use below command to list all the tables present in it.

sqlite>  SELECT name FROM geek_db.sqlite_master WHERE type='table';

we can see the output as below,

Thus after connecting a database file using ATTACH, we can perform all the operations on the attached database. For example, update, insertion, deletion, creation of tables can be performed.

Conclusion

Hence SQLite supports ATTACH or ATTACH DATABASE statement to connect a particular database file and perform operations. In addition, it also supports working with multiple databases or database files simultaneously. Also previously attached databases can be removed using DETACH DATABASE command.