{"id":630,"date":"2021-02-25T04:30:41","date_gmt":"2021-02-25T04:30:41","guid":{"rendered":"https:\/\/info.juliusgoh.life\/?p=630"},"modified":"2021-02-25T04:48:13","modified_gmt":"2021-02-25T04:48:13","slug":"mysql-search-through-whole-database-for-a-certain-string","status":"publish","type":"post","link":"https:\/\/info.juliusgoh.life\/?p=630","title":{"rendered":"MYSQL search through whole database for a certain string."},"content":{"rendered":"<p>The code above will generate a procedure called get_table(SEARCH_STRING) , that will accept 1 parameter which is your string. It will search through the whole database server besides these db(information_schema,test,mysql). But if you would like to search only in a certain DB you may use the second block.<br \/>\n<i>$ call get_table(&#8216;STRING&#8217;);<\/i><\/p>\n<pre>\r\n## FIRST BLOCK\r\n## Table for storing resultant output\r\n\r\nCREATE TABLE `temp_details` (\r\n `t_schema` varchar(45) NOT NULL,\r\n `t_table` varchar(45) NOT NULL,\r\n `t_field` varchar(45) NOT NULL\r\n) ENGINE=MyISAM DEFAULT CHARSET=latin1;\r\n\r\n## Procedure for search in all fields of all databases\r\nDELIMITER $$\r\n#Script to loop through all tables using Information_Schema\r\nDROP PROCEDURE IF EXISTS get_table $$\r\nCREATE PROCEDURE get_table(in_search varchar(50))\r\n READS SQL DATA\r\nBEGIN\r\n DECLARE trunc_cmd VARCHAR(50);\r\n DECLARE search_string VARCHAR(250);\r\n\r\n DECLARE db,tbl,clmn CHAR(50);\r\n DECLARE done INT DEFAULT 0;\r\n DECLARE COUNTER INT;\r\n\r\n DECLARE table_cur CURSOR FOR\r\n SELECT concat('SELECT COUNT(*) INTO @CNT_VALUE FROM `',table_schema,'`.`',table_name,'` WHERE `', column_name,'` REGEXP ''',in_search,''';')\r\n ,table_schema,table_name,column_name\r\n FROM information_schema.COLUMNS\r\n WHERE TABLE_SCHEMA NOT IN ('information_schema','test','mysql');\r\n\r\n DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;\r\n\r\n #Truncating table for refill the data for new search.\r\n PREPARE trunc_cmd FROM \"TRUNCATE TABLE temp_details;\";\r\n EXECUTE trunc_cmd ;\r\n\r\n OPEN table_cur;\r\n table_loop:LOOP\r\n FETCH table_cur INTO search_string,db,tbl,clmn;\r\n\r\n #Executing the search\r\n SET @search_string = search_string;\r\n SELECT search_string;\r\n PREPARE search_string FROM @search_string;\r\n EXECUTE search_string;\r\n\r\n\r\n SET COUNTER = @CNT_VALUE;\r\n SELECT COUNTER;\r\n\r\n IF COUNTER&gt;0 THEN\r\n # Inserting required results from search to table\r\n INSERT INTO temp_details VALUES(db,tbl,clmn);\r\n END IF;\r\n\r\n IF done=1 THEN\r\n LEAVE table_loop;\r\n END IF;\r\n END LOOP;\r\n CLOSE table_cur;\r\n\r\n #Finally Show Results\r\n SELECT * FROM temp_details;\r\nEND $$\r\nDELIMITER ;\r\n<\/pre>\n<p>This is basically same as the above but only search a certain database, below are the steps to use this Procedure:-<br \/>\n<i>$ call get_table(&#8216;STRING&#8217;,&#8217;DBNAME&#8217;);<\/i><\/p>\n<pre>\r\n## SECOND BLOCK\r\n## Table for storing resultant output\r\n\r\nCREATE TABLE `temp_details` (\r\n `t_schema` varchar(45) NOT NULL,\r\n `t_table` varchar(45) NOT NULL,\r\n `t_field` varchar(45) NOT NULL\r\n) ENGINE=MyISAM DEFAULT CHARSET=latin1;\r\n\r\n## Procedure for search in all fields of all databases\r\nDELIMITER $$\r\n#Script to loop through all tables using Information_Schema\r\nDROP PROCEDURE IF EXISTS get_table $$\r\nCREATE PROCEDURE get_table(in_search varchar(50),dbName varchar(50))\r\n READS SQL DATA\r\nBEGIN\r\n DECLARE trunc_cmd VARCHAR(50);\r\n DECLARE search_string VARCHAR(250);\r\n\r\n DECLARE db,tbl,clmn CHAR(50);\r\n DECLARE done INT DEFAULT 0;\r\n DECLARE COUNTER INT;\r\n\r\n DECLARE table_cur CURSOR FOR\r\n SELECT concat('SELECT COUNT(*) INTO @CNT_VALUE FROM `',table_schema,'`.`',table_name,'` WHERE `', column_name,'` REGEXP ''',in_search,''';')\r\n ,table_schema,table_name,column_name\r\n FROM information_schema.COLUMNS\r\n WHERE TABLE_SCHEMA IN (dbName);\r\n\r\n DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;\r\n\r\n #Truncating table for refill the data for new search.\r\n PREPARE trunc_cmd FROM \"TRUNCATE TABLE temp_details;\";\r\n EXECUTE trunc_cmd ;\r\n\r\n OPEN table_cur;\r\n table_loop:LOOP\r\n FETCH table_cur INTO search_string,db,tbl,clmn;\r\n\r\n #Executing the search\r\n SET @search_string = search_string;\r\n SELECT search_string;\r\n PREPARE search_string FROM @search_string;\r\n EXECUTE search_string;\r\n\r\n\r\n SET COUNTER = @CNT_VALUE;\r\n SELECT COUNTER;\r\n\r\n IF COUNTER>0 THEN\r\n # Inserting required results from search to table\r\n INSERT INTO temp_details VALUES(db,tbl,clmn);\r\n END IF;\r\n\r\n IF done=1 THEN\r\n LEAVE table_loop;\r\n END IF;\r\n END LOOP;\r\n CLOSE table_cur;\r\n\r\n #Finally Show Results\r\n SELECT * FROM temp_details;\r\nEND $$\r\nDELIMITER ;\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>The code above will generate a procedure called get_table(SEARCH_STRING) , that will accept 1 parameter which is your string. It will search through the whole database server besides these db(information_schema,test,mysql). But if you would like to search only in a certain DB you may use the second block. $ call get_table(&#8216;STRING&#8217;); ## FIRST BLOCK ## [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":217,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"image","meta":{"footnotes":""},"categories":[3],"tags":[],"_links":{"self":[{"href":"https:\/\/info.juliusgoh.life\/index.php?rest_route=\/wp\/v2\/posts\/630"}],"collection":[{"href":"https:\/\/info.juliusgoh.life\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/info.juliusgoh.life\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/info.juliusgoh.life\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/info.juliusgoh.life\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=630"}],"version-history":[{"count":4,"href":"https:\/\/info.juliusgoh.life\/index.php?rest_route=\/wp\/v2\/posts\/630\/revisions"}],"predecessor-version":[{"id":638,"href":"https:\/\/info.juliusgoh.life\/index.php?rest_route=\/wp\/v2\/posts\/630\/revisions\/638"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/info.juliusgoh.life\/index.php?rest_route=\/wp\/v2\/media\/217"}],"wp:attachment":[{"href":"https:\/\/info.juliusgoh.life\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=630"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/info.juliusgoh.life\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=630"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/info.juliusgoh.life\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=630"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}