How to find data for all tables from a specific column?

Question

SQL

There is a file SQLITE3 base.db


Contains many tables.
Each table has a referer column name, you need to search all data tables from the referer column, for example, a word containing yandex from the referer column

Answer 1

We generate a query for all tables:

SELECT 
    GROUP_CONCAT('select "' || name || '" table_name, * from `' || name || '` where referer = "yandex"', ' union ') q
FROM 
    sqlite_master 
WHERE 
    type ='table' AND 
    name NOT LIKE 'sqlite_%';

We get:

select "t1" table_name, * from t1 where referer = "yandex" union select "t2" table_name, * from t2 where referer = "yandex"

We do:

+============+====+=========+
| table_name | id | referer |
+============+====+=========+
| t1         | 2  | yandex  |
+------------+----+---------+
| t2         | 20 | yandex  |
+------------+----+---------+

SQLite fiddle

Answer 2

Through SHOW TABLES you get a list of all tables and in turn do a search in each or form a UNION query.