What will you do if one day some one ask you to find single string in all databases, alltables 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.

1. ## Table for storing resultant output
2. CREATE TABLE `temp_details` (
3. `t_schema` varchar(45) NOT NULL,
4. `t_table` varchar(45) NOT NULL,
5. `t_field` varchar(45) NOT NULL
6. ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
7. ## Procedure for search in all fields of all databases
8. DELIMITER $$
9. #Script to loop through all tables using Information_Schema
10. DROP PROCEDURE IF EXISTS get_table $$
11. CREATE PROCEDURE get_table(in_search varchar(50))
12. READS SQL DATA
13. BEGIN
14. DECLARE trunc_cmd VARCHAR(50);
15. DECLARE search_string VARCHAR(250);
16. DECLARE db,tbl,clmn CHAR(50);
17. DECLARE done INT DEFAULT 0;
18. DECLARE COUNTER INT;
19. DECLARE table_cur CURSOR FOR
20. SELECT concat(‘SELECT COUNT(*) INTO @CNT_VALUE FROM `’,table_schema,’`.`’,table_name,’` WHERE `’, column_name,’` REGEXP ”’,in_search,”’;')
21. ,table_schema,table_name,column_name
22. FROM information_schema.COLUMNS
23. WHERE TABLE_SCHEMA NOT IN (‘information_schema’,'test’,'mysql’);
24. DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
25. #Truncating table for refill the data for new search.
26. PREPARE trunc_cmd FROM “TRUNCATE TABLE temp_details;”;
27. EXECUTE trunc_cmd ;
28. OPEN table_cur;
29. table_loop:LOOP
30. FETCH table_cur INTO search_string,db,tbl,clmn;
31. #Executing the search
32. SET @search_string = search_string;
33. SELECT  search_string;
34. PREPARE search_string FROM @search_string;
35. EXECUTE search_string;
36. SET COUNTER = @CNT_VALUE;
37. SELECT COUNTER;
38. IF COUNTER>0 THEN
39. # Inserting required results from search to table
40. INSERT INTO temp_details VALUES(db,tbl,clmn);
41. END IF;
42. IF done=1 THEN
43. LEAVE table_loop;
44. END IF;
45. END LOOP;
46. CLOSE table_cur;
47. #Finally Show Results
48. SELECT * FROM temp_details;
49. END $$
50. DELIMITER ;

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

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 decideing to adopt the easy way out though its v.old.

Related posts:

1. Ideas for select all columns but one mysql stored procedure Assume we’ve a table with 100 rows and we need...
2. Stored procedure to add-remove prefix by rename table mysql Here is one more procedure – (this time) for mass...
3. Stored procedure – Execute query if table or Column exists Well procedures mainly carried out working with information schema and...