MySQL query to find all tables in a database for a row?

Question

Is there a mySQL query to find all tables in a database?

If you can not search all the tables in the database from the Workbench mySQL GUI?

From phpmyadmin there is a search bar that you can use to select all the tables to search. I believe this is super efficient since magento, the e-commerce package I work with, contains hundreds of tables and various product details in different tables.

Answer 1

If you want to do this exclusively in MySQL, without the help of any programming language, you can use this:

## Table for storing resultant output CREATE TABLE `temp_details` ( `t_schema` varchar(45) NOT NULL, `t_table` varchar(45) NOT NULL, `t_field` varchar(45) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; ## 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 NOT IN ('information_schema','test','mysql'); 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 * FROM temp_details; END $$ DELIMITER ; 

Source: http://forge.mysql.com/tools/tool.php?id=232

Answer 2

If you use the MySQL Workbench , you can do this by right-clicking on the database schema that you want to execute, and then " Search Table Data ... ".

Here you can select the option Search using REXEXP "and then enter the search text as usual. It will provide DB strings matching your specific text.

You will also need to check the "Search for columns of all types" field.

Answer 3

Alternatively, if your database is not so large, you can dump and search the generated .sql file.

Answer 4

In MySQL Workbench, you can use the Data Search in Table feature. It can search across multiple tables and / or multiple databases.

Answer 5

Finding a row in all database tables is challenging. Usually you don’t need to use exactly all the tables, and the results are difficult to read without a specific layout (table tree with matches or the like)

SQL Workbench / J offers a graphical interface and command line version for this task:

Additional Information:

• http://www.sql-workbench.net/manual/wb-commands.html#command-search-data
• http://www.sql-workbench.net/manual/dbexplorer.html#search-table-data

NOTE. A search with a JDBC driver uses a lot of memory if it is configured incorrectly. SQL Workbench / J warns about this, and although the online documentation is a bit dated, the documentation sources (doc / xml / db-problem.xml) explain how to fix it for different BBDDs:

Here is an excerpt for Postgres:

The PostgreSQL JDBC driver, by default, buffers results from the database in memory before returning them to the application. This means that when & wb-productname; uses (for a short time) twice as much memory as is really necessary. It also means that WbExport or WbCopy will effectively read the entire result into memory before writing it to the output file. For large exports, this is usually not required. This driver behavior can be modified so that the driver uses cursor-based searches. To do this, the "Autocommit" parameter must be disabled in the connection profile, and the default sample size must be set, which is greater than zero. A recommended value, for example, 10, it is possible that higher numbers give better performance. The number specified for the sample size determines the number of lines that the driver stores in the internal buffer before requesting more lines from the backend.