Search / find through all databases, tables, columns in MySQL

By
Kedar
– October 29, 2009

What will you do if one day some one ask you to find single string in all databases, all tables and in all columns?

I just read such question and tried to find a “ready made” solution.

Reusability is Key Concept !!

But I ended up finding no “copy-paste” material. Some of the posts like http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm helped me out and supported my thinking of “how to do it” !

Here is how I did it – Search through all databases - tables - columns:

  • Create a table for storing output.
  • Loop through information_schema database’s COLUMNS table to obtain alldatabases, table and column names.
  • Execute a count(*) query on database.table for each column with appropriate search string in where condition.
  • If count(*) > 0, that perticular column has the search term.
  • Insert that triplet (database name, table name, column name) in to a table.
  • Select * from table to view respective database,table and column names having the search term.

## Procedure for search in all fields of all databases
DELIMITER $$
#Script to loop through all tables using Information_Schema
DROP PROCEDURE IF EXISTS get_table $$
CREATE PROCEDURE get_table(in_search varchar(50))
READS SQL DATA
BEGIN
DECLARE trunc_cmd VARCHAR(50);
DECLARE search_string VARCHAR(250);
DECLARE db,tbl,clmn CHAR(50);
DECLARE done INT DEFAULT 0;
DECLARE COUNTER INT;
DECLARE table_cur CURSOR FOR
SELECT concat('SELECT COUNT(*) INTO @CNT_VALUE FROM `',table_schema,'`.`',table_name,'` WHERE `', column_name,'` REGEXP "',in_search,'"') ,table_schema,table_name,column_name FROM information_schema.COLUMNS WHERE TABLE_SCHEMA IN ('network_detail');
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
#Truncating table for refill the data for new search.
PREPARE trunc_cmd FROM "TRUNCATE TABLE temp_details;";
EXECUTE trunc_cmd ;
OPEN table_cur;
table_loop:LOOP
FETCH table_cur INTO search_string,db,tbl,clmn;
#Executing the search
SET @search_string = search_string;
#SELECT search_string;
PREPARE search_string FROM @search_string;
EXECUTE search_string;
SET COUNTER = @CNT_VALUE;
#SELECT COUNTER;
IF COUNTER>0 THEN
# Inserting required results from search to table
INSERT INTO temp_details VALUES(db,tbl,clmn);
END IF;
IF done=1 THEN
LEAVE table_loop;
END IF;
END LOOP;
CLOSE table_cur;
#Finally Show Results
SELECT concat("SELECT * FROM ",t_table, " WHERE ", t_field, " REGEXP '", in_search, "';") FROM temp_details;
END $$
DELIMITER ;

No wonder you will find the variable naming has not been taken care of; but its forgivable!

Download the code here: find in all databases-tables.sql

Well this thing worked for me in 5.0.83-community-nt-log on windows machine. I dropped idea of creating temporary table through procedure to store and display results considering a bug and deciding to adopt the easy way out though its v.old.

You might also like::

  1. Perl Script for Analyze – Optimize – Repair Mysql Databases
  2. Choosing between MyISAM and INNODB – MySQL Storage Engines
  3. Difference MyISAM and InnoDB Storage Engines Mysql
Tags: , , , , , ,