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.

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