Search / find through all databases, tables, columns in MySQL
What will you do if one day some one ask you to find single string in all databases, all tables and in all columns, In MySQL Database?
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 the idea for how to 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.
MySQL Procedure for search in all fields of all databases
## 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.
I get an error : “mydb.temp_details does not exist”.
In your script i don’t see where you declare it. Is something missing in the script ?
Thanks for your help, very useful idea i was looking for !
I’d also like to know how this SP can be transformed to a script, throwing the ouptut, like a regular statement.
Hi Kedar,
thanks for useful article.
result table temp_details contains query like “select * from table where column
regexp ‘search_string'”.
I want to get the result of this queries as output.
is it possible.
Nice one. Works well enough for me. Saved me a whole lot of time.
Thx for your effort.
This very help full. Grt sql
Hi,
Could you please show me a sample if I want to search by keywords ‘book’
Thanks,
Sham
Hi Sham,
Follow this command on your linux terminal:
1. wget http://kedar.nitty-witty.com/blog/wp-content/uploads/2009/10/find-in-all-databases-tables.sql_.txt
2. cat find-in-all-databases-tables.sql_.txt | mysql DBNAME
3.
mysql> use DBNAME
mysql> call get_table(‘book’);
Hope this helps.
Hi Kedar,
It helps. Thanks. But I can’t see the result. I tested in phpmyadmin.
]
The results show
Please advice. Thanks.
Thanks,
Sham
screenshot of test code => awesomescreenshot.com/0dc4jl3ufc
screenshot of result => awesomescreenshot.com/02c4jl4r19
Thanks it helped verry much
3Q,it help me.
Hi Brian,
Yes, you can search a single DB with a small change in the code.
Instead of “WHERE TABLE_SCHEMA NOT IN (‘information_schema’,’test’,’mysql’);” @ line #23
you can use
“WHERE TABLE_SCHEMA IN (‘YOUR_DB_NAME’);”
i.e. NOT IN replaced with IN.
Hope that helps,
Kedar.
Is there a way to apply this to a single database instead?