How to List Tables in SQLite Attached Databases

When you attach an SQLite database using the ATTACH command, you can list tables by querying the sqlite_master table system catalog or using the .tables command in the sqlite3 shell. To view table contents, you need to prefix table names with the database alias using syntax like SELECT * FROM attached_database.table_name;.

Contents

• Understanding SQLite Database Attachment
• Listing Tables in Attached Databases
• Listing Table Contents from Attached Databases
• Practical Examples and Commands
• Advanced Techniques for Database Exploration

Understanding SQLite Database Attachment

The SQLite ATTACH command allows you to access multiple database files within a single connection session. When you attach a database, it gets a temporary name (alias) that you can use to reference it in your queries.

The basic syntax for attaching a database is:

ATTACH 'database_file_path' AS database_alias;

For example:

ATTACH 'mydata.db' AS mydata;

After attachment, the database becomes part of the current connection but remains separate from the main database. This allows you to work with multiple databases simultaneously while maintaining data isolation.

Listing Tables in Attached Databases

There are several methods to list tables in an attached SQLite database:

Method 1: Using sqlite_master Table

The sqlite_master table is a system catalog that contains metadata about all database objects. You can query it with a database prefix:

SELECT name FROM mydata.sqlite_master 
WHERE type='table' 
ORDER BY name;

Replace mydata with your database alias. This query returns all table names from the attached database.

Method 2: Using the .tables Command

In the sqlite3 shell, the .tables command can list tables from all attached databases:

.tables

To list tables from a specific database only, use:

.tables mydata.*

Method 3: Using the .schema Command

The .schema command shows the CREATE TABLE statements for all tables in the attached database:

.schema mydata.

This will display the complete table definitions for all tables in the mydata database.

Method 4: Querying All Databases

To list tables from all databases including the main database:

SELECT name, sql FROM sqlite_master 
WHERE type='table' 
ORDER BY name;

Listing Table Contents from Attached Databases

Once you know the table names in your attached database, you can query their contents using the database prefix:

Basic SELECT Queries

-- Select all columns from a specific table
SELECT * FROM mydata.customers;

-- Select specific columns
SELECT id, name FROM mydata.products;

-- Add WHERE conditions
SELECT * FROM mydata.orders 
WHERE order_date > '2023-01-01';

Joining Tables Across Databases

You can even join tables from different databases:

SELECT m.name, p.product_name
FROM mydata.customers m
JOIN mydata.products p ON m.favorite_product = p.id;

Listing All Tables and Their Contents

To see all tables and a sample of their contents:

-- Generate queries to list all tables and their contents
SELECT 'SELECT ''' || name || ''' AS table_name, * FROM ' || name || ' LIMIT 5;' AS sample_query
FROM mydata.sqlite_master
WHERE type='table'
ORDER BY name;

This will generate SELECT statements that show the first 5 rows from each table.

Practical Examples and Commands

Complete Workflow Example

Here’s a complete workflow showing how to attach a database and explore it:

# Start sqlite3
sqlite3

# Attach the database
sqlite> ATTACH 'mydatabase.db' AS mydata;

# List tables in attached database
sqlite> .tables mydata.*

# Get table schema
sqlite> .schema mydata.customers

# View table contents
sqlite> SELECT * FROM mydata.customers LIMIT 10;

# Generate summary information
sqlite> SELECT 
         name AS table_name,
         sql AS table_definition
         FROM mydata.sqlite_master
         WHERE type='table'
         ORDER BY name;

Programmatic Approach

If you’re working with SQLite programmatically (Python example):

import sqlite3

# Connect to main database
conn = sqlite3.connect(':memory:')  # or main database file

# Attach another database
conn.execute("ATTACH 'mydatabase.db' AS mydata")

# List tables in attached database
cursor = conn.execute("""
    SELECT name 
    FROM mydata.sqlite_master 
    WHERE type='table' 
    ORDER BY name
""")
tables = [row[0] for row in cursor.fetchall()]

print("Tables in attached database:", tables)

# Query table contents
for table in tables:
    cursor = conn.execute(f"SELECT * FROM mydata.{table} LIMIT 3")
    print(f"\nFirst 3 rows from {table}:")
    for row in cursor.fetchall():
        print(row)

Advanced Techniques for Database Exploration

Getting Database Schema Information

-- Get detailed schema information
SELECT 
    name AS table_name,
    sql AS create_statement,
    COUNT(*) AS column_count
FROM mydata.sqlite_master
WHERE type='table'
GROUP BY name, sql
ORDER BY table_name;

Analyzing Table Structure

-- Get column information for all tables
SELECT 
    m.name AS table_name,
    p.name AS column_name,
    p.type AS column_type,
    p.'notnull' AS not_null,
    p.dflt_value AS default_value
FROM mydata.sqlite_master m
JOIN mydata.sqlite_master p ON m.name = p.tbl_name
WHERE m.type='table' AND p.type='table' AND p.name LIKE 'sqlite%'
ORDER BY m.name, p.cid;

Database Comparison

Compare table structures between databases:

-- Compare tables in main database vs attached database
SELECT 
    mydata.name AS attached_table,
    main.name AS main_table,
    CASE 
        WHEN mydata.name = main.name THEN 'Same name'
        ELSE 'Different name'
    END AS comparison
FROM mydata.sqlite_master mydata
LEFT JOIN main.sqlite_master main ON mydata.name = main.name
WHERE mydata.type='table' AND main.type='table'
ORDER BY mydata.name;

Dynamic Query Generation

Generate queries to explore all tables systematically:

-- Generate queries to count rows in all tables
SELECT 
    'SELECT ''' || name || ''' AS table_name, COUNT(*) AS row_count FROM ' || name || ';' AS count_query
FROM mydata.sqlite_master
WHERE type='table'
ORDER BY name;

Conclusion

• Database attachment is essential when working with multiple SQLite files, using the ATTACH 'file_path' AS alias; command
• List tables in attached databases by querying sqlite_master with the database prefix or using .tables alias.* in the shell
• Query table contents by prefixing table names with the database alias: SELECT * FROM alias.table_name;
• Practical workflow: Attach database → List tables → Check schema → Query contents → Analyze data
• Advanced techniques include generating dynamic SQL, comparing databases, and programmatic database exploration

The most efficient approach combines the .tables command for quick exploration with SQL queries for detailed analysis. Remember to always prefix table names with the database alias when querying attached databases to avoid ambiguity.

Sources

1. Official SQLite Documentation - ATTACH
2. SQLite System Catalog - sqlite_master
3. SQLite Command Line Shell Documentation
4. SQLite Data Types and Schema