Script to search entire database in MySql

We are working with MySql version 5.0.91 and we are searching for a script that can search all the tables within a database for specific value.  In our MSSQL we have a script that does that excellently but can't seem to find one for MySql.  Also, we are working in Workbench 6.2
rayluvsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rayluvsAuthor Commented:
FYI, below is the script in MSSQL that we use to search a database (in case needed for searching a similiar in MySql):

/****************************************
* SEARCH VALUES WITHIN A DB
****************************************/

Declare @SearchStr nvarchar(100)
set @SearchStr='RICCS'


CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName = 
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
       ) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END 
END

SELECT ColumnName, ColumnValue FROM #Results
DROP TABLE #Results

Open in new window


We understand that the next step if no script is found is just convert our MSSQL script to MySql, but we hope that EE can help us find one.
0
Olaf DoschkeSoftware DeveloperCommented:
Your MSSQL script is very rudimenary only searching all fields containing textual data, you can't eg search for a date or number.

I'd rather do something similar with PHP than as a Workbench MySQL script.

Ingredients for this are querying the list of tables in a database and the list of fields with their type, which is done using SHOW TABLES and SHOW COLUMNS.

Bye, Olaf.
0
rayluvsAuthor Commented:
So how would you do it in PHP?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Olaf DoschkeSoftware DeveloperCommented:
If I have the time, I'll do it within the following week. Since my parents celebrate gold marriage I'll not be active before monday.

In short use SHOW TABLES to get a list of tables
Use SHOW COLUMNS for each table name to retrieve a list of columns,
put together queries and collect results.

About the same way the MSSQL script does it.

It's no magic, you can do it by just learning very basic PHP how to query databases, query into arrays (for example) and concatenate them.

Bye, Olaf.
0
Steve BinkCommented:
What environmental requirements do you have?  Does it have to run inside MySQL, or is an external environment acceptable (e.g., command line, PHP, etc)?
0
rayluvsAuthor Commented:
We trying to treat MySql as we do with MsSql; we query the database.  Since we are not expert on MySql and wanted to query the database, we downloaded Workbench so we can get familiarize with the sql engine and work it (as with MsSql we use MsSql Mng. Studio).

As to environment we have both, Workbench for query-scripting  and PHPcoder for PHP.

We prefer the actual query script to work in workbench, but if its best for you in PHP, it's ok.
0
Steve BinkCommented:
Here's one you can try, courtesy of http://kedar.nitty-witty.com/blog/search-through-all-databases-tables-columns-in-mysql:
 ## 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 ; 

Open in new window

0
rayluvsAuthor Commented:
it return a "0 rows affected".  We think that we need to place the value searching.  Went thru the script but can't seem to see find; where do we place the value to search for?
0
Steve BinkCommented:
That SQL only creates the procedure.  You still have to call it with the proper search term: get_table('my search text')
0
Olaf DoschkeSoftware DeveloperCommented:
Fine, that save me working something out. It would have been odd, if there wouldn't have been anything similar for MySQL, as the general idea is simple, iterate meta data about tables and columns and query results.

One general remark: Those things are not performing good, don't make use of fulltext indexing and may only help find to locate the table where you find some value you see in UI, if you want to get grip on a databse you didn't design yourself.

Bye, Olaf.
0
rayluvsAuthor Commented:
Sorry for the delay.

To run the procedure script, we run "CALL get_table('my search text')", but it says "table doesn't exist".  Went thru the database and could not found the store procedure created.  Ran it again and below is the result:

msql
0
Olaf DoschkeSoftware DeveloperCommented:
Congratulations. You created the procedure. Now, that it exists, it can be used.

Bye, Olaf.
0
rayluvsAuthor Commented:
Where is it created? (can't find it)
0
Olaf DoschkeSoftware DeveloperCommented:
In the database. 2nd line of your log is green, and so the procedure was created. In whatever current database. You have to tell us, you connected to some database and executed the script creating the stored procedure.

Now you only need CALL get_table('search') and this will return a result table.

Bye, Olaf.
0
rayluvsAuthor Commented:
But it is not there.  Below is a pic of Workbench screen database area.  See Store Procedure, it's empty.

wb
When running "CALL" at the MySql prompt, it also say it doesn't exist.


ERROR 1146 (42S02): Table 'nooo.temp_details' doesn't exist
0
Steve BinkCommented:
I've edited the code to account for the error you reported:
 ## 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; 
####  Old code...
 ##Truncating table for refill the data for new search. 
 #PREPARE trunc_cmd FROM "TRUNCATE TABLE temp_details;"; 
 #EXECUTE trunc_cmd ; 
#### New code...
 DROP TEMPORARY TABLE IF EXISTS temp_details;
 CREATE TEMPORARY TABLE temp_details (tdb VARCHAR(255), ttbl VARCHAR(255), tclmn VARCHAR(255));
 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 (tdb,ttbl,tclmn) 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 ",tdb,".",ttbl," WHERE ", tclmn, " REGEXP '", in_search, "';") FROM temp_details; 
 DROP TEMPORARY TABLE IF EXISTS temp_details;
 END $$ 
 DELIMITER ; 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rayluvsAuthor Commented:
Same error (see below).  For some reason it does not create the SP.

result2
Also note, no SP created
sp
0
Steve BinkCommented:
Try running this query:
SELECT db,name,type FROM mysql.proc;

Open in new window

0
rayluvsAuthor Commented:
Ok will try in a bit
0
Olaf DoschkeSoftware DeveloperCommented:
The error you get when you CALL get_table is "Table 'nooo.temp_details' doesn't exist" , not "get_details doesn't exist".
The SP exists, workbench just doesn't display it. The only problem you have is the temp table not created, maybe because it already existed from a previous half run. Steve mended that.

In regard of what Workbench displays, I have seen others report SP not showing up. It can be a version mismatch between Workbench and MySQL.

Bye, Olaf.
0
rayluvsAuthor Commented:
Haven't tried the script yet, but the CALL was also tried via MySQL prompt, beside the apps Workbench.

We'll inform result of the SELECT.
0
Olaf DoschkeSoftware DeveloperCommented:
Yes, and the call succeeded in itself, you just came across an error of the SP itself, trying to create a temp table, which already existed. That is telling the SP exists.

Please check your Workbench and MySQL versions. And please do as Steve said and do
SELECT db,name,type FROM mysql.proc;

Open in new window

This should show you the SP is there, just not shown in Workbench.

Even if you have the latest newest Workbench installed, the MySQL version might be too old to understand the queries Workbench does to determine such meta data as existing tables, SPs etc. for its display.

Bye, Olaf.
0
rayluvsAuthor Commented:
Yes, the SP exist (see pic) and also now the "Store Procedure" in Workbench has the arrow (see next pic) which was not there before.

sp2
wb2
We checked the tables in the DB and temp_details is not there.  Is it supposed to be in a specific place?
0
Olaf DoschkeSoftware DeveloperCommented:
temp tables will not be seen in workbench. They automatically cease to exist after a session expires.
Steve has given you an amended store proc code, that will drop the temp table, if it exists and then create it new. It's only needed during the run of the proc, not before or afterwards. Lookup the explanation of  CREATE TEMPORARY TABLE.

Bye, Olaf.
0
rayluvsAuthor Commented:
You are correct.  Ok will do.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.