How can I list the tables in a SQLite database file that was opened with ATTACH?

Listing Tables in ATTACHed SQLite Databases

Learn how to effectively query and list tables within SQLite database files that have been attached using the ATTACH DATABASE command, a common task for managing complex database environments.

SQLite's ATTACH DATABASE command is a powerful feature that allows you to connect multiple database files to a single database connection. This is incredibly useful for tasks like migrating data, performing cross-database queries, or simply organizing related data into separate files. However, once a database is attached, you might find yourself needing to inspect its schema, specifically listing the tables it contains. This article will guide you through the methods to achieve this, ensuring you can always navigate your attached databases with ease.

Understanding ATTACHed Databases

When you ATTACH a database, SQLite assigns it an alias (or schema name). This alias acts as a prefix for all objects (tables, views, etc.) within that attached database. The primary database, the one you initially opened, is typically referred to as main, and any temporary objects reside in the temp schema. Understanding this naming convention is crucial for correctly querying attached databases.

flowchart TD
    A[SQLite Connection] --> B{main.db}
    A --> C{attached_db_1.db}
    A --> D{attached_db_2.db}
    B -- Queries --> E[main.table_name]
    C -- Queries --> F[alias1.table_name]
    D -- Queries --> G[alias2.table_name]

Conceptual diagram of a SQLite connection with multiple attached databases.

Method 1: Querying sqlite_master for Attached Databases

The sqlite_master table (or sqlite_schema in newer SQLite versions) is a special table present in every SQLite database that stores the schema definition for that database. To list tables from an attached database, you simply need to prefix sqlite_master with the alias you assigned during the ATTACH command.

ATTACH DATABASE 'path/to/your/attached.db' AS attached_db;

SELECT name FROM attached_db.sqlite_master WHERE type='table';

Attaching a database and listing its tables using sqlite_master.

Method 2: Using the .tables Command in the SQLite CLI

If you are working within the SQLite command-line interface (CLI), there's a convenient dot command specifically designed for listing tables. While .tables by itself lists tables in the main database, you can specify the attached database's alias to inspect its contents.

.open main.db
ATTACH DATABASE 'path/to/your/attached.db' AS attached_db;

.tables attached_db

Using the .tables command in SQLite CLI to list tables from an attached database.

Listing All Tables Across All Attached Databases

Sometimes, you might want a comprehensive list of all tables, regardless of which database file they belong to. You can achieve this by querying the sqlite_master table for each attached database or by using a more advanced query that leverages the PRAGMA database_list command to dynamically get all attached database names.

ATTACH DATABASE 'path/to/db1.db' AS db1;
ATTACH DATABASE 'path/to/db2.db' AS db2;

SELECT
  tbl_name,
  'main' AS database_name
FROM main.sqlite_master
WHERE type = 'table'
UNION ALL
SELECT
  tbl_name,
  'db1' AS database_name
FROM db1.sqlite_master
WHERE type = 'table'
UNION ALL
SELECT
  tbl_name,
  'db2' AS database_name
FROM db2.sqlite_master
WHERE type = 'table';

Listing tables from multiple attached databases using UNION ALL.

By mastering these techniques, you can efficiently manage and inspect the schema of your attached SQLite databases, making your database development and administration tasks much smoother.