Search all tables in a SQLite database for a specific column

search_sqlite_for_column.py

import sqlite3

def search_in_all_tables(db_path, text):
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
    tables = [t[0] for t in cursor.fetchall()]
    results = []

    for table in tables:
        cursor.execute(f"PRAGMA table_info({table})")
        columns = [c[1] for c in cursor.fetchall()]
        for col in columns:
            query = f"SELECT rowid, * FROM {table} WHERE {col} LIKE ?"
            cursor.execute(query, (f"%{text}%",))
            found = cursor.fetchall()
            for row in found:
                results.append((table, col, row))
    conn.close()
    return results
  
results = search_in_all_tables('/Users/abeals/Library/Group Containers/243LU875E5.groups.com.apple.podcasts/Documents/MTLibrary.sqlite', '1000470600560')
for result in results:
  print(result)
  print()