1

The DB I'm working on has roughly 30 tables that might contain different pieces of data I'd like to locate. Not an ideal situation but hey ho.

Given two strings...

  • string1 - value that appears in table name.
  • string2 - value to be searched for text-like columns.

...I'd like to search all tables with name matching string1 for columns that have a datatype in...

  • text
  • char
  • varchar

... for data rows like "%string2%".

What I'd ideally see returned is a resultset with a row for each string1, string2 hit, in a format like:

  • table_name_string1_appears_in
  • colum_name_string2_appears_in
  • value_from_id_col_of_table_with_name_like_string1
  • data_from_cell_matching_string2

Can a pure mysql solution exist for this that is re-usable?

blip blop
  • 11
  • 1
  • this would have to be a dynamic query built using `information_schema`. Have you tried to write it? – Barmar May 02 '13 at 10:17
  • Some SQL clients offer this kind of tools. Which one are you using? –  May 02 '13 at 10:33
  • possible duplicate of [mySQL query to search all tables within a database for a string?](http://stackoverflow.com/questions/7523039/mysql-query-to-search-all-tables-within-a-database-for-a-string) – echo_Me May 02 '13 at 10:33
  • Thanks @Barmar, I am investigating. @a_horse, my mysql client is the the mysql CLI :) `mysql Ver 14.14 Distrib 5.1.66, for debian-linux-gnu (x86_64) using readline 6.1`. @echo_me. The answer by @entropid in the linked question looks like good building blocks, thanks. – blip blop May 02 '13 at 10:47

2 Answers2

0

A php-workaround (not pure sql) is to have your 30 tables' name to be stored on an array. foreach each of them and evaluate whether the table name is part of your aray value. If it's true, then run a %string2% select query on that table name

Willy Pt
  • 1,795
  • 12
  • 20
  • Thanks Willy, I'm hoping some SQL can do the lot. I've encountered this situation a few times before, but seem to overcome it in a unique way each time. I've always previously used another language... – blip blop May 02 '13 at 10:24
0

if you want search for string in all your database

then do this

   mysqldump -u myuser --no-create-info databasename > myfile.sql

and after find your string in myfile.sql

or try this solution here

Community
  • 1
  • 1
echo_Me
  • 37,078
  • 5
  • 58
  • 78