Introduction

Searching for a text value across every table in a MySQL database is possible, but MySQL does not provide one built-in command that safely scans every text column everywhere. The usual solution is to inspect INFORMATION_SCHEMA, generate per-table search queries, and run them deliberately. This is a useful debugging and data-audit technique, but it is also expensive, so you should treat it as an investigation tool rather than an everyday query pattern.

Start by Finding Candidate Text Columns

The first step is identifying which columns are worth searching. Usually that means text-like types such as char, varchar, text, mediumtext, and longtext.

1SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
2FROM INFORMATION_SCHEMA.COLUMNS
3WHERE TABLE_SCHEMA = 'app_db'
4  AND DATA_TYPE IN ('char', 'varchar', 'text', 'tinytext', 'mediumtext', 'longtext')
5ORDER BY TABLE_NAME, ORDINAL_POSITION;

This query gives you the searchable surface area of the database. Restricting the search to text-like columns avoids meaningless LIKE checks on numeric or date columns.

Generate Search SQL Dynamically

Once you know the columns, you can build per-table WHERE clauses. A simple pattern is grouping text columns per table and concatenating LIKE predicates.

1SELECT CONCAT(
2  'SELECT ''', TABLE_NAME, ''' AS table_name FROM `', TABLE_NAME,
3  '` WHERE ',
4  GROUP_CONCAT(CONCAT('`', COLUMN_NAME, '` LIKE ''%needle%''') SEPARATOR ' OR '),
5  ' LIMIT 1;'
6) AS generated_sql
7FROM INFORMATION_SCHEMA.COLUMNS
8WHERE TABLE_SCHEMA = 'app_db'
9  AND DATA_TYPE IN ('char', 'varchar', 'text', 'tinytext', 'mediumtext', 'longtext')
10GROUP BY TABLE_NAME;

This does not execute the queries automatically. It generates them so you can inspect or run them deliberately.

That manual review step matters because broad searches can be expensive and may need further filtering.

Use Application Code When You Need Better Control

For operational searches, a small script is often clearer than pushing everything into one stored SQL statement. A script can:

• paginate through tables
• log matches cleanly
• parameterize the search safely
• skip large or irrelevant tables

1import mysql.connector
2
3conn = mysql.connector.connect(host="localhost", user="app", password="secret", database="app_db")
4cur = conn.cursor()
5
6cur.execute("""
7    SELECT TABLE_NAME, COLUMN_NAME
8    FROM INFORMATION_SCHEMA.COLUMNS
9    WHERE TABLE_SCHEMA = %s
10      AND DATA_TYPE IN ('char', 'varchar', 'text', 'tinytext', 'mediumtext', 'longtext')
11""", ("app_db",))
12
13by_table = {}
14for table, column in cur.fetchall():
15    by_table.setdefault(table, []).append(column)
16
17needle = "%alice%"
18for table, columns in by_table.items():
19    where = " OR ".join(f"`{col}` LIKE %s" for col in columns)
20    sql = f"SELECT * FROM `{table}` WHERE {where} LIMIT 5"
21    cur.execute(sql, [needle] * len(columns))
22    rows = cur.fetchall()
23    if rows:
24        print(table, len(rows))

This keeps query generation explicit and avoids some of the awkwardness of pure SQL meta-programming.

Expect Performance Costs

A database-wide text scan is naturally expensive. LIKE '%text%' cannot use ordinary indexes effectively because the pattern starts with a wildcard. On large tables, this often becomes a full scan.

That means you should:

• run it sparingly
• narrow by schema or table when possible
• use LIMIT during exploration
• avoid doing it on production hot paths unless absolutely necessary

If this kind of search is a regular application requirement, the answer is usually not better LIKE generation. The answer is proper full-text indexing or a dedicated search system.

Distinguish Investigation from Product Features

Searching all fields in every table is reasonable for troubleshooting, migration audits, or forensic cleanup. It is usually a bad design for user-facing application search. Production search features should rely on defined searchable columns and indexing strategy, not dynamic scans of the entire relational schema.

That distinction keeps database debugging tools from silently becoming application architecture.

Common Pitfalls

• Running LIKE '%text%' across every column without restricting the scan to text-like types.
• Building dynamic SQL unsafely and interpolating raw search text directly into queries.
• Forgetting that wildcard-leading LIKE searches usually force full scans.
• Treating a cross-database investigation query as if it were a normal application search pattern.
• Executing broad scans in production without table limits, scheduling, or operational awareness.

Summary

• MySQL does not have one built-in command to search every field in every table.
• Use INFORMATION_SCHEMA.COLUMNS to discover text-like columns first.
• Generate per-table search queries dynamically and run them deliberately.
• Prefer a small script when you need safer parameterization and cleaner reporting.
• For recurring search requirements, use indexing or a search-specific design instead of database-wide LIKE scans.