MySQL search value methods

If you want to find a specific value anywhere in a MySQL database (any column, any table), there are a few common approaches—ranging from quick-and-dirty to more systematic.

---

1️⃣ Search a known table but any column

If you know the table but not the column:

SELECT *
FROM your_table
WHERE col1 = 'value'
   OR col2 = 'value'
   OR col3 = 'value';

⚠️ This requires you to list columns manually.

---

2️⃣ Search all columns of all tables (automatic way)

This is the practical solution when you have no idea where the value lives.

Step 1: Generate search queries using INFORMATION_SCHEMA

SELECT CONCAT(
  'SELECT "', TABLE_NAME,
  '" AS table_name, "', COLUMN_NAME,
  '" AS column_name FROM ', TABLE_SCHEMA, '.', TABLE_NAME,
  ' WHERE ', COLUMN_NAME, ' = ''your_value'';'
)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_database';

This generates SQL statements for every column in every table.

Step 2: Copy & run the generated queries

Each result will show you:

• which table
• which column
• where the value exists

---

3️⃣ Search using LIKE (partial match)

Useful for text values:

SELECT CONCAT(
  'SELECT "', TABLE_NAME,
  '" AS table_name, "', COLUMN_NAME,
  '" AS column_name FROM ', TABLE_SCHEMA, '.', TABLE_NAME,
  ' WHERE ', COLUMN_NAME, ' LIKE ''%your_value%'';'
)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_database'
  AND DATA_TYPE IN ('char','varchar','text','mediumtext','longtext');

---

4️⃣ One-table-at-a-time brute force (quick hack)

If the table is small and you’re in a hurry:

SELECT *
FROM your_table
WHERE CONCAT_WS('|', col1, col2, col3, col4)
      LIKE '%your_value%';

Not fast. Not elegant. But effective for debugging 😄

---

5️⃣ Performance & safety tips

• 🔥 Avoid running this on production databases with large tables
• ⏱️ These queries can be slow (full table scans)
• 🧪 Best used in dev / staging
• 🔐 Make sure you have access to INFORMATION_SCHEMA

---

Which approach should you use?

Situation	Best method
Know table, not column	Manual OR
Know nothing	INFORMATION_SCHEMA
Partial text match	LIKE
Debugging small tables	CONCAT_WS

---

Total page views: