Enumerating Tables in Attached SQLite Databases: CLI Methods and Schema Queries

Understanding Table Enumeration in Main and Attached SQLite Databases

Core Challenge: Retrieving Table Lists Across Multiple Attached Databases

When working with SQLite databases, users often attach multiple database files to a single session to facilitate cross-database queries. A common task in this scenario is enumerating the tables present not just in the main database but also in attached databases. The SQLite command-line interface (CLI) provides the .tables command for listing tables, but its default behavior and interaction with attached databases can lead to confusion. This guide dissects the mechanics of table enumeration, explores why attached databases might not appear as expected, and provides robust solutions for querying table metadata across all connected schemas.

Mechanics of Table Visibility in SQLite Sessions

1. Database Attachment and Schema Scope

SQLite allows attaching additional databases to a session using the ATTACH DATABASE command or the .attach CLI directive. Each attached database is assigned an alias (e.g., _tz_ or a in the forum examples). Tables within these databases are referenced using the schema.table syntax (e.g., _tz_.TZ_Countries).

However, the .tables CLI command’s output varies depending on:

• SQLite version: Older versions may not prefix table names with schema aliases.
• CLI configuration settings: The .mode and .header settings influence output formatting.
• Ephemeral databases: Databases created via VACUUM INTO or in-memory instances might not persist long enough to inspect.

2. The Role of the sqlite_schema Table

Every SQLite database (main, temp, or attached) contains a sqlite_schema table (formerly sqlite_master) that stores metadata about database objects (tables, indexes, views, triggers). Querying sqlite_schema directly allows precise control over which tables are listed:

SELECT name FROM sqlite_schema WHERE type='table' ORDER BY name; -- Main database
SELECT name FROM attached_alias.sqlite_schema WHERE type='table'; -- Specific attached database

3. The pragma_table_list Pragma

Introduced in SQLite 3.37.0 (2021-11-27), the pragma_table_list virtual table provides a unified view of tables across all attached databases:

SELECT schema, name FROM pragma_table_list WHERE schema NOT IN ('temp', 'main');

This pragma returns columns like schema (database alias), name (table name), and type (e.g., ‘table’, ‘view’), making it ideal for cross-database inventory tasks.

Root Causes of Missing Tables in Attached Databases

1. Misapplication of VACUUM INTO for Database Copies

The VACUUM INTO 'filename' command creates a new database by copying the main database’s content into the specified file. Critical nuances:

• The new database is not automatically attached to the current session.
• For in-memory databases (file::memory:), the database is destroyed immediately after creation because no connection holds it open.

Example Misstep:

VACUUM INTO 'file::memory:'; -- Creates and closes an ephemeral in-memory copy
.tables -- Only lists tables in the original main database

Here, the user expects the VACUUM INTO command to attach the new database, but it merely creates a transient copy that vanishes once the command completes.

2. Schema Prefixes Omitted in .tables Output

In SQLite CLI versions before 3.37.0, .tables might not prepend schema aliases to table names, especially if the CLI is configured for compatibility with older tools. This leads to output where tables from attached databases appear merged with main database tables, making them indistinguishable.

Example Ambiguity:

sqlite> .tables
Album   Artist  _tz_.Country  _tz_.Zone

Without consistent schema prefixes, users cannot discern which tables belong to attached databases.

3. Incorrect or Unintended Database Attachment

Attaching a database requires precise syntax:

ATTACH DATABASE 'path/to/file.db' AS schema_alias;

Errors in the file path, alias naming, or file permissions prevent successful attachment, leaving the database’s tables inaccessible. Silent failures (e.g., attaching a non-existent file in write-ahead log mode) exacerbate this issue.

Comprehensive Solutions for Cross-Database Table Enumeration

1. Direct Querying of sqlite_schema with Schema Prefixes

To list tables in a specific attached database:

-- Replace 'schema_alias' with the attached database’s alias
SELECT name FROM schema_alias.sqlite_schema 
WHERE type IN ('table', 'view') 
AND name NOT LIKE 'sqlite_%'; -- Exclude internal schemas

Example Output:

┌──────────────┐
│    name     │
├──────────────┤
│ TZ_Countries │
│ TZ_Zones    │
└──────────────┘

2. Leveraging pragma_table_list for Unified Inventory

For SQLite ≥3.37.0, pragma_table_list provides a holistic view:

SELECT schema, name, type 
FROM pragma_table_list 
WHERE schema NOT IN ('main', 'temp') -- Filter out default schemas
ORDER BY schema, name;

Output:

┌──────────────┬──────────────────┬───────┐
│   schema    │      name       │ type  │
├──────────────┼──────────────────┼───────┤
│ _tz_       │ TZ_Countries    │ table │
│ _tz_       │ TZ_Zones       │ table │
│ a          │ a             │ table │
└──────────────┴──────────────────┴───────┘

3. Correct Use of VACUUM INTO with Explicit Attachment

To create a persistent copy of the main database and attach it:

VACUUM INTO '/path/to/copy.db'; -- Creates a physical file
ATTACH DATABASE '/path/to/copy.db' AS copy_db;
.tables copy_db.* -- CLI command to list tables in 'copy_db'

In-Memory Database Workaround:
To retain an in-memory database, attach it first and populate it using ATTACH + INSERT:

ATTACH DATABASE 'file::memory:' AS mem_db;
CREATE TABLE mem_db.demo AS SELECT * FROM main.some_table;
.tables mem_db.*

4. CLI Configuration for Explicit Schema Prefixes

Force the CLI to display schema prefixes using:

.mode list
.headers on
SELECT schema || '.' || name AS full_name 
FROM pragma_table_list 
WHERE type = 'table';

Custom .tables Replacement:
Create a CLI macro to emulate .tables with schemas:

sqlite> .shell echo "SELECT schema || '.' || name FROM pragma_table_list WHERE type='table';" | sqlite3 current.db

5. Validating Database Attachments

Confirm attached databases using:

PRAGMA database_list;

Output:

┌─────┬──────────────┬──────────────────────┐
│ seq │    name     │         file         │
├─────┼──────────────┼──────────────────────┤
│ 0  │ main       │ /path/to/main.db    │
│ 2  │ _tz_      │ /path/to/tz_data.db │
└─────┴──────────────┴──────────────────────┘

Advanced Techniques and Edge Cases

Handling Ephemeral and Temporary Databases

• In-Memory Databases: Use ATTACH DATABASE 'file::memory:?cache=shared' AS mem to create a shared in-memory database accessible across connections.
• Temporary Tables: Tables created with CREATE TEMP TABLE reside in the temp schema. Use .tables temp.* or query temp.sqlite_schema.

Dynamic SQL Generation for Schema Enumeration

For environments with many attached databases, dynamically generate queries:

SELECT 'SELECT "' || name || '" AS schema, name FROM ' || name || '.sqlite_schema WHERE type="table";' 
FROM pragma_database_list 
WHERE name NOT IN ('main', 'temp');

Execute the resulting statements to collate tables from all schemas.

Cross-Database Joins and Shadow Tables

Be aware of shadow tables (e.g., sqlite_stat1) and virtual tables that may appear in sqlite_schema. Filter them using:

SELECT * FROM pragma_table_list WHERE name NOT LIKE 'sqlite_%';

By methodically applying these strategies, users can overcome the limitations of default CLI behaviors and achieve precise control over table enumeration in multi-database SQLite environments. Whether troubleshooting attachment issues, recovering from VACUUM INTO misunderstandings, or leveraging modern pragmas, this guide equips developers with the depth needed to navigate SQLite’s schema landscape confidently.