{"id":38,"date":"2009-10-29T08:04:35","date_gmt":"2009-10-29T08:04:35","guid":{"rendered":"http:\/\/kedar.nitty-witty.com\/?p=38"},"modified":"2026-02-23T11:17:13","modified_gmt":"2026-02-23T11:17:13","slug":"search-through-all-databases-tables-columns-in-mysql","status":"publish","type":"post","link":"https:\/\/kedar.nitty-witty.com\/blog\/search-through-all-databases-tables-columns-in-mysql","title":{"rendered":"how to search \/ find through all databases, tables, columns in MySQL"},"content":{"rendered":"\n

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? This article presents a practical solution to search across all your MySQL databases, tables, and columns using stored procedures, making the process re-usable and straightforward.<\/p>\n\n\n\n

I prepared the dynamic stored procedure to perform the whole MySQL database search.<\/p>\n\n\n

\n
\"\"<\/figure>\n<\/div>\n\n\n

Step-by-Step Guide to perform MySQL database search<\/h3>\n\n\n\n

Here is the idea for how to search through all databases – tables – columns:<\/p>\n\n\n\n

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

    MySQL database search – all columns, tables and databases.<\/h2>\n\n\n\n\n\n\n\n

    Below is a MySQL database search stored procedure that enables searching through all fields of all databases:<\/p>\n\n\n\n

    ## Procedure for search in all fields of all databases\nDELIMITER $$\n\n#Script to loop through all tables using Information_Schema\nDROP PROCEDURE IF EXISTS get_table $$\nCREATE PROCEDURE get_table(in_search varchar(50))\nREADS SQL DATA\nBEGIN\n\nDECLARE trunc_cmd VARCHAR(50);\nDECLARE search_string VARCHAR(250);\nDECLARE db,tbl,clmn CHAR(250);\nDECLARE done INT DEFAULT 0;\nDECLARE COUNTER INT;\nDECLARE table_cur CURSOR FOR\nSELECT 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 NOT IN ('mysql','information_schema', 'performance_schema', 'sys');\n\nDECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;\n\n#Drop and recreate table for storing the data for new search.\nPREPARE trunc_cmd FROM \"DROP TABLE IF EXISTS temp_details;\";\nEXECUTE trunc_cmd ;\n\nCREATE TEMPORARY TABLE temp_details (\ndb varchar(250),\ntbl varchar(250),\nclmn varchar(250)\n);\n\n\nOPEN table_cur;\n\ntable_loop:LOOP\nFETCH table_cur INTO search_string,db,tbl,clmn;\n#Executing the search\nSET @search_string = search_string;\n#SELECT  search_string;\nPREPARE search_string FROM @search_string;\nEXECUTE search_string;\nSET COUNTER = @CNT_VALUE;\n#SELECT COUNTER;\nIF COUNTER>0 THEN\n# Inserting required results from search to table\nSET SESSION SQL_MODE='';\nINSERT INTO temp_details (db,tbl,clmn) VALUES(db,tbl,clmn);\nEND IF;\nIF done=1 THEN\nLEAVE table_loop;\n\nEND IF;\nEND LOOP;\nCLOSE table_cur;\n\n#Finally Show Results\nSELECT  * FROM temp_details;\nEND $$\nDELIMITER ;\n\n\n\n<\/code><\/pre>\n\n\n\n
    No wonder you will find the variable naming has not been taken care of; but its forgivable!<\/p>\n\n\n\n
    Download the code here: find in all databases-tables.sql<\/a><\/strong><\/h2>\n\n\n\n
    Well this thing worked for me in 5.0.83-community-nt-log on windows machine. This MySQL database search procedure is also been tested against MySQL 8. I dropped idea of creating temporary table through procedure to store and display results considering a bug<\/a> and deciding to adopt the easy way out though its very old.<\/p>\n\n\n\n
    Conclusion<\/strong><\/h2>\n\n\n\n
    Searching for a specific string across numerous databases, tables, and columns in MySQL can be a complex task. However, by implementing the provided stored procedure, you can efficiently and comprehensively perfom MySQL database search. This approach enhances reusability, saves time, and ensures accurate results, making it an invaluable tool for database administrators and developers.<\/p>\n","protected":false},"excerpt":{"rendered":"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? This article presents a practical solution…\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[8,378],"tags":[305,190,427,672,674,189,108,426],"class_list":["post-38","post","type-post","status-publish","format-standard","category-mysql","category-mysql-scripts-mysql","tag-all-table-all-columns","tag-find-in-tables","tag-mysql","tag-mysql-database-search","tag-search-all-tables-columns-databases","tag-search-in-all-databases","tag-stored-procedure","tag-technical"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t