TL;DR - MySQL has no built-in SEARCH ALL TABLES command. You need to query INFORMATION_SCHEMA.COLUMNS to generate per-column SELECT statements dynamically. - The fastest manual method: run a dynamic SQL query against INFORMATION_SCHEMA that builds UNION queries across every text column in your database. Full query below. - A stored procedure wraps this into a reusable CALL search_all_tables('your_value') that returns every match with table name, column name, and matching row. - phpMyAdmin has a built-in Search tab at the database level that searches all tables in one click — no SQL required. - Adminer has a similar "Search data in all tables" link on the database overview page. - For databases with 100+ tables, limit your search to string columns (VARCHAR, TEXT, CHAR) to avoid scanning every integer and datetime column.

Table of Contents

• Introduction
• Why MySQL Does Not Have a Global Search
• Method 1: Dynamic SQL with INFORMATION_SCHEMA
• Method 2: Stored Procedure for Reusable Search
• Method 3: phpMyAdmin Search Tab
• Method 4: Adminer Search
• Method 5: Browser-Based Search with DBEverywhere
• Performance Considerations
• FAQ
• Conclusion

How to Search All Tables in a Database for a Specific Value

You know the email address is in the database somewhere. A customer is asking about their account, a GDPR deletion request just came in, or you are debugging a foreign key relationship and need to find every table that references a specific user ID. The problem: your database has 40 tables and you have no idea which ones contain the value you are looking for. You need to search all tables in a database for that one string, and MySQL does not give you a single command to do it.

This comes up constantly. A 2024 Stack Overflow survey showed that over 52% of professional developers work with MySQL or MariaDB. According to DB-Engines, MySQL has held the #1 or #2 spot in database popularity rankings for over a decade. Yet the platform still has no native full-database search. Every developer who works with MySQL long enough eventually faces this problem and ends up writing the same workaround.

This guide covers four methods to find a value across every table in a MySQL database — from raw SQL you can paste into any client to GUI tools that do it in two clicks.

Why MySQL Does Not Have a Global Search

Relational databases are designed for structured queries against known tables and columns. A global search across all tables is essentially a full table scan of every table in the database — the kind of operation that database engines are specifically optimized to avoid.

Consider what happens when you search for a string across a database with 50 tables averaging 500,000 rows each. That is 25 million rows to scan, and if each table has 10 string columns, the engine is checking 250 million column values. MySQL's query optimizer cannot use indexes for LIKE '%value%' searches (the leading wildcard prevents index usage), so every one of those checks is a sequential scan.

PostgreSQL added full-text search capabilities that make this slightly less painful, and some tools like Apache Solr and Elasticsearch exist specifically for this use case. But in MySQL, you need one of the workarounds below.

Method 1: Dynamic SQL with INFORMATION_SCHEMA

MySQL's INFORMATION_SCHEMA.COLUMNS table contains metadata about every column in every table in your database. You can query it to generate SELECT statements for every string column, then execute those statements to find your value.

Step 1: Generate the Search Queries

This query builds a set of SQL statements that will search every VARCHAR, TEXT, CHAR, MEDIUMTEXT, and LONGTEXT column in your database:

SELECT CONCAT(
    'SELECT ''', TABLE_NAME, ''' AS table_name, ''',
    COLUMN_NAME, ''' AS column_name, `',
    COLUMN_NAME, '` AS matching_value FROM `',
    TABLE_NAME, '` WHERE `',
    COLUMN_NAME, '` LIKE ''%YOUR_SEARCH_VALUE%'''
) AS search_query
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_database_name'
  AND DATA_TYPE IN ('varchar', 'char', 'text', 'mediumtext', 'longtext', 'tinytext')
ORDER BY TABLE_NAME, COLUMN_NAME;

Replace YOUR_SEARCH_VALUE with the value you are looking for and your_database_name with your database name.

This returns a result set where each row is a complete SELECT statement. For a database with 200 string columns, you get 200 rows of SQL.

Step 2: Run the Generated Queries

Copy the output, join the statements with UNION ALL, and execute them as a single query. If your database is small enough, you can paste the entire block into your MySQL client.

For a more automated approach, use a prepared statement to execute the generated SQL directly:

SET SESSION group_concat_max_len = 1000000;

SET @search_value = 'user@example.com';
SET @db_name = DATABASE();

SELECT GROUP_CONCAT(
    CONCAT(
        'SELECT ''', TABLE_NAME, ''' AS tbl, ''',
        COLUMN_NAME, ''' AS col, `',
        COLUMN_NAME, '` AS val FROM `',
        TABLE_NAME, '` WHERE `',
        COLUMN_NAME, '` LIKE ''%', @search_value, '%'''
    )
    SEPARATOR ' UNION ALL '
) INTO @search_sql
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @db_name
  AND DATA_TYPE IN ('varchar', 'char', 'text', 'mediumtext', 'longtext', 'tinytext');

PREPARE stmt FROM @search_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

This builds the entire UNION ALL query as one string using GROUP_CONCAT, then executes it. The group_concat_max_len setting is increased from its default of 1,024 bytes because the generated SQL for a database with dozens of tables will easily exceed that limit. MySQL silently truncates GROUP_CONCAT results that exceed the limit — one of those defaults that causes subtle bugs.

The result shows every match: the table name, the column name, and the actual matching value.

Important: this approach uses string concatenation to build SQL, which means the search value is injected directly into the query. Only use this in a trusted context (your own database client), never in application code exposed to user input.

Method 2: Stored Procedure for Reusable Search

If you find yourself searching across tables regularly, wrap the logic in a stored procedure so you can call it with a single command.

DELIMITER $$

CREATE PROCEDURE search_all_tables(IN search_term VARCHAR(255))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE tbl_name VARCHAR(255);
    DECLARE col_name VARCHAR(255);
    DECLARE cur CURSOR FOR
        SELECT TABLE_NAME, COLUMN_NAME
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND DATA_TYPE IN ('varchar', 'char', 'text', 'mediumtext', 'longtext', 'tinytext');
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    DROP TEMPORARY TABLE IF EXISTS search_results;
    CREATE TEMPORARY TABLE search_results (
        table_name VARCHAR(255),
        column_name VARCHAR(255),
        matching_value TEXT
    );

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO tbl_name, col_name;
        IF done THEN
            LEAVE read_loop;
        END IF;

        SET @sql = CONCAT(
            'INSERT INTO search_results ',
            'SELECT ''', tbl_name, ''', ''', col_name, ''', `', col_name, '` ',
            'FROM `', tbl_name, '` ',
            'WHERE `', col_name, '` LIKE ''%', search_term, '%'''
        );

        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;

    CLOSE cur;

    SELECT * FROM search_results;
    DROP TEMPORARY TABLE search_results;
END$$

DELIMITER ;

Now you can search with:

CALL search_all_tables('user@example.com');

The procedure iterates through every string column in the current database, runs a LIKE search against each one, and collects matches into a temporary table. When it finishes, it returns all matches at once.

Performance note: on a database with 50 tables and 300 string columns, this procedure typically completes in 2-15 seconds for tables with under 100,000 rows each. For tables with millions of rows, expect it to take proportionally longer since every search is a full table scan.

Method 3: phpMyAdmin Search Tab

If you use phpMyAdmin, you already have a built-in global search — and most people never find it because it is at the database level, not the table level.

Here is how to use it:

1. Select your database from the left sidebar (click the database name, not a table).
2. Click the Search tab in the top navigation bar. This tab only appears when you have a database selected, not when you are viewing a specific table.
3. In the "Search in database" form:
4. Enter your search value in the Words or values to search for field.
5. Select the match type: at least one of the words, all of the words, the exact phrase, as regular expression, or LIKE %...% (substring match). For finding a specific email or ID, use the exact phrase or LIKE %...%.
6. Below the search field, you will see a list of all tables in the database with checkboxes. By default, all tables are selected. Uncheck tables you know are irrelevant to speed up the search.
7. Click Go.

phpMyAdmin returns a results page showing: - Which tables had matches (with match counts) - A Show search results link for each table that had hits - Clicking a result shows the matching rows with options to edit or delete them

This is by far the easiest way to search all tables in a database for a specific value if you have phpMyAdmin available. It performs the same LIKE queries under the hood that the manual SQL methods use, but the interface handles all the query generation and result aggregation for you.

Limitation: phpMyAdmin's database-level Search only searches text columns by default. If you need to find a numeric value (like a user ID across foreign key columns), you may need to select "all" column types in the search options, which increases the search scope significantly.

Method 4: Adminer Search

Adminer (the single-file alternative to phpMyAdmin) also has a built-in full-database search, and it is even easier to find.

1. Connect to your database in Adminer and select it.
2. On the database overview page (where you see the list of all tables), look for the Search data in all tables link near the top of the page.
3. Enter your search value and click Search.

Adminer searches across all tables and returns a summary showing which tables contain the value, how many rows matched, and a link to view those rows. Adminer supports MySQL, MariaDB, PostgreSQL, SQLite, MS SQL, Oracle, Elasticsearch, and MongoDB — so this method works across database engines, not just MySQL.

Adminer's search is notably faster than phpMyAdmin's for databases with many tables because of its lighter architecture. The entire tool is a single PHP file under 500 KB compared to phpMyAdmin's 13+ MB installation footprint.

Method 5: Browser-Based Search with DBEverywhere

Both the phpMyAdmin and Adminer methods above require that you have one of those tools installed and connected to your database. If you do not — or if your database is behind a firewall and you cannot install anything on the server — you can use the same search features through DBEverywhere.

DBEverywhere hosts phpMyAdmin and Adminer in the cloud, so you get the full Search tab functionality without installing anything:

1. Go to dbeverywhere.com and enter your database connection details.
2. A phpMyAdmin or Adminer session starts in your browser within seconds.
3. Use the Search tab (phpMyAdmin) or "Search data in all tables" link (Adminer) exactly as described above.

This is particularly useful for managed databases (AWS RDS, DigitalOcean Managed Databases, Google Cloud SQL) where you cannot install phpMyAdmin on the database server itself. DBEverywhere connects from a static IP address that you whitelist once in your database firewall.

The free tier gives you 5 sessions per month. The paid tier ($5/mo) adds unlimited sessions and saved connections so you can reconnect with one click.

Performance Considerations

Searching all tables is inherently expensive. Here are ways to keep it manageable:

Limit to string columns. The SQL methods above already filter on DATA_TYPE IN ('varchar', 'char', 'text', ...). If you are searching for a numeric value like a user ID, change the filter to include int, bigint, etc. — but be aware that searching numeric columns with LIKE forces MySQL to cast them to strings, which is slower than an equality check. For numeric searches, use = search_value instead of LIKE '%search_value%'.

Exclude large tables you know are irrelevant. Log tables, audit tables, and session tables often have millions of rows and are unlikely to contain the value you are looking for. Add AND TABLE_NAME NOT IN ('logs', 'audit_trail', 'sessions') to your INFORMATION_SCHEMA query.

Use exact match when possible. WHERE column = 'value' can use an index if one exists. WHERE column LIKE '%value%' never can (because of the leading wildcard). WHERE column LIKE 'value%' (trailing wildcard only) can use an index. If you know the exact value, always use = instead of LIKE.

Consider full-text indexes for recurring searches. If you frequently search for text across specific large tables, MySQL's FULLTEXT indexes provide dramatically better performance. A MATCH() AGAINST() query on an indexed column completes in milliseconds compared to seconds or minutes for a LIKE '%value%' scan. According to MySQL benchmarks, full-text search on a 1 million row table is roughly 100-200x faster than an equivalent LIKE query.

Watch your group_concat_max_len. MySQL's default group_concat_max_len is 1,024 bytes. A database with 100 string columns will generate a GROUP_CONCAT result far exceeding that limit, and MySQL truncates silently — you will get a partial query that throws a syntax error. Set it to at least 1 MB (SET SESSION group_concat_max_len = 1000000) before running the dynamic SQL method.

FAQ

Can I search all tables in a PostgreSQL database?

Yes, but the syntax differs. PostgreSQL does not have a direct equivalent to MySQL's INFORMATION_SCHEMA.COLUMNS-based approach, but you can query information_schema.columns (lowercase) with similar logic. PostgreSQL also supports the \dt and \d meta-commands in psql for listing tables and columns. Adminer supports PostgreSQL and includes the same "Search data in all tables" feature. DBEverywhere provides Adminer for PostgreSQL databases, so the browser-based method works for Postgres too.

How long does it take to search all tables in a large database?

For a database with 50 tables, 300 string columns, and 10 million total rows, expect the search to take 10-60 seconds depending on hardware and whether the data fits in the buffer pool. MySQL's InnoDB buffer pool caches frequently accessed data in memory — if your database is smaller than the buffer pool (typically 128 MB to 8 GB depending on configuration), subsequent searches will be significantly faster than the first one. On a database with 100+ million rows, a full search can take several minutes.

Will searching all tables lock my database or affect production?

The SELECT queries used in all methods above are read operations, so they do not lock tables for writes on InnoDB (MySQL's default engine since 5.5, which accounts for the vast majority of MySQL installations). However, they do consume CPU and I/O. Running a full-database search on a production server during peak traffic is not recommended — it adds load to the server and can slow down other queries competing for the same buffer pool pages and CPU cycles. If possible, run the search on a read replica or during off-peak hours.

Is there a way to search binary or blob columns?

Yes, but it requires converting the binary data. Use HEX() or CAST() in your search query: WHERE HEX(blob_column) LIKE '%48454C4C4F%' (where 48454C4C4F is the hex representation of "HELLO"). This is extremely slow on large blob columns and rarely practical. If you regularly need to search binary data, consider storing a text representation alongside the blob.

Can I search for a value and replace it across all tables?

The methods in this article are read-only — they find where a value exists but do not modify it. To search and replace, you would extend the stored procedure to generate UPDATE statements instead of SELECT statements. Be extremely careful with this: run the search first to understand which tables and columns contain the value, back up your database, and test the UPDATE on a staging copy before touching production. There is no undo for an UPDATE without a backup.

Conclusion

MySQL does not have a built-in command to search all tables in a database for a specific value, but the workarounds are well-established: query INFORMATION_SCHEMA.COLUMNS to generate dynamic SQL, wrap it in a stored procedure for reuse, or use the database-level Search tab in phpMyAdmin or Adminer's "Search data in all tables" feature.

For a quick one-time search, the phpMyAdmin or Adminer approach is the fastest — two clicks, no SQL to write. For databases where you cannot install tools, the dynamic SQL method works in any MySQL client. And if you need a reusable solution, the stored procedure turns it into a single CALL statement.

If you need phpMyAdmin or Adminer's search functionality but do not have either tool installed, DBEverywhere gives you full access from any browser — connect to your database, click Search, and find your value in seconds.

Try DBEverywhere free — search your database from any browser