I'm trying to figure out how to locate all occurrences of a url in a database. I want to search all tables and all fields. But I have no idea where to start or if it's even possible.
This question is related to
mysql
The answer is
If you can use a bash - here is a script: It needs a user dbread with pass dbread on the database.
#!/bin/bash
IFS='
'
DBUSER=dbread
DBPASS=dbread
echo -n "Which database do you want to search in (press 0 to see all databases): "
read DB
echo -n "Which string do you want to search: "
read SEARCHSTRING
for i in `mysql $DB -u$DBUSER -p$DBPASS -e "show tables" | grep -v \`mysql $DB -u$DBUSER -p$DBPASS -e "show tables" | head -1\``
do
for k in `mysql $DB -u$DBUSER -p$DBPASS -e "desc $i" | grep -v \`mysql $DB -u$DBUSER -p$DBPASS -e "desc $i" | head -1\` | grep -v int | awk '{print $1}'`
do
if [ `mysql $DB -u$DBUSER -p$DBPASS -e "Select * from $i where $k='$SEARCHSTRING'" | wc -l` -gt 1 ]
then
echo " Your searchstring was found in table $i, column $k"
fi
done
done
If anyone wants an explanation: http://infofreund.de/?p=1670
In unix machines, if the database is not too big:
mysqldump -u <username> -p <password> <database_name> --extended=FALSE | grep <String to search> | less -S
Scott gives a good example of how to do it, but the question is why would you want to? If you need to do a find-and-replace on a specific string, you could also try doing a mysqldump of your database, do a find-and-replace in an editor, then re-load the database.
Maybe if you gave some background on what you are trying to achieve, others might be able to provide better answers.
In phpMyAdmin a 'Search' feature is available:
- Select particular database not table.
- Click 'Search' tab
- Enter the search term you want
- Select the tables you want to search in
phpMyAdmin screen shot:
The 'Search' feature is also available in MySQL Workbench:
- Database Menu > Search Table Data
- Select database and tables you want to search (it will search in selected tables only)
- In search you can use wildChars.
MySQL Workbench screen shot:
You can do this by using HeidiSQL without generating Db dumps
Steps:
1) Select the database you need to search in from the left panel of GUI.
2) Export > Export Database as SQL
3) In Table Tools window select "FIND TEXT" tab.
4) Provide your string to search and click "FIND".
5) It will list all the tables contains our string.
6) Select the row with higher relevance %.
7) Click "SEE RESULTS"
I was looking for the same but couldn't find it, so I make a small script in PHP, you can find it at: http://tequilaphp.wordpress.com/2010/07/05/searching-strings-in-a-database-and-files/
Good luck! (I remove some private code, let me know if I didn't break it in the process :D)
Not an elegant solution, but you could achieve it with a nested looping structure
// select tables from database and store in an array
// loop through the array
foreach table in database
{
// select columns in the table and store in an array
// loop through the array
foreach column in table
{
// select * from table where column = url
}
}
You could probably speed this up by checking which columns contain strings while building your column array, and also by combining all the columns per table in one giant, comma-separated WHERE clause.
Using the MySQL Workbench, you can search for a string from the "Database" -> "Search Table Data" menu option.
Specify LIKE %URL_TO_SEARCH% and on the left side select all the tables you want to search through. You can use "Cntrl + A" to select the whole tree on the left, and then deselect the objects you don't care about.
The first 30 seconds of this video shows how to use the global search feature of Phpmyadmin and it works. it will search every table for a string.
http://www.vodahost.com/vodatalk/phpmyadmin-setup/62422-search-database-phpmyadmin.html
I was looking for this myself when we changed domain on our Wordpress website. It can't be done without some programming so this is what I did.
<?php
header("Content-Type: text/plain");
$host = "localhost";
$username = "root";
$password = "";
$database = "mydatabase";
$string_to_replace = 'old.example.com';
$new_string = 'new.example.com';
// Connect to database server
mysql_connect($host, $username, $password);
// Select database
mysql_select_db($database);
// List all tables in database
$sql = "SHOW TABLES FROM ".$database;
$tables_result = mysql_query($sql);
if (!$tables_result) {
echo "Database error, could not list tables\nMySQL error: " . mysql_error();
exit;
}
echo "In these fields '$string_to_replace' have been replaced with '$new_string'\n\n";
while ($table = mysql_fetch_row($tables_result)) {
echo "Table: {$table[0]}\n";
$fields_result = mysql_query("SHOW COLUMNS FROM ".$table[0]);
if (!$fields_result) {
echo 'Could not run query: ' . mysql_error();
exit;
}
if (mysql_num_rows($fields_result) > 0) {
while ($field = mysql_fetch_assoc($fields_result)) {
if (stripos($field['Type'], "VARCHAR") !== false || stripos($field['Type'], "TEXT") !== false) {
echo " ".$field['Field']."\n";
$sql = "UPDATE ".$table[0]." SET ".$field['Field']." = replace(".$field['Field'].", '$string_to_replace', '$new_string')";
mysql_query($sql);
}
}
echo "\n";
}
}
mysql_free_result($tables_result);
?>
Hope it helps anyone who's stumbling into this problem in the future :)
SQLyog is GUI based solution to the problem of data search across all columns, tables and databases. One can customize search restricting it to field, table and databases.
In its Data Search feature one can search for strings just like one uses Google.
I can't remember where I came across this script, but I've been using it with XCloner to move my WP multisites.
<?php
// Setup the associative array for replacing the old string with new string
$replace_array = array( 'FIND' => 'REPLACE', 'FIND' => 'REPLACE');
$mysql_link = mysql_connect( 'localhost', 'USERNAME', 'PASSWORD' );
if( ! $mysql_link) {
die( 'Could not connect: ' . mysql_error() );
}
$mysql_db = mysql_select_db( 'DATABASE', $mysql_link );
if(! $mysql_db ) {
die( 'Can\'t select database: ' . mysql_error() );
}
// Traverse all tables
$tables_query = 'SHOW TABLES';
$tables_result = mysql_query( $tables_query );
while( $tables_rows = mysql_fetch_row( $tables_result ) ) {
foreach( $tables_rows as $table ) {
// Traverse all columns
$columns_query = 'SHOW COLUMNS FROM ' . $table;
$columns_result = mysql_query( $columns_query );
while( $columns_row = mysql_fetch_assoc( $columns_result ) ) {
$column = $columns_row['Field'];
$type = $columns_row['Type'];
// Process only text-based columns
if( strpos( $type, 'char' ) !== false || strpos( $type, 'text' ) !== false ) {
// Process all replacements for the specific column
foreach( $replace_array as $old_string => $new_string ) {
$replace_query = 'UPDATE ' . $table .
' SET ' . $column . ' = REPLACE(' . $column .
', \'' . $old_string . '\', \'' . $new_string . '\')';
mysql_query( $replace_query );
}
}
}
}
}
mysql_free_result( $columns_result );
mysql_free_result( $tables_result );
mysql_close( $mysql_link );
echo 'Done!';
?>
MikeW presented an interesting solution, but as mentioned in comments, it's a SQL Server solution not a MySQL solution. Here is a MySQL solution:
use information_schema;
set @q = 'Boston';
set @t = 'my_db';
select CONCAT('use \'',@q,'\';') as q UNION
select CONCAT('select \'', tbl.`TABLE_NAME`,'\' as TableName, \'', col.`COLUMN_NAME`,'\' as Col, `',col.`COLUMN_NAME`,'` as value from `' , tbl.`TABLE_NAME`,'` where `' ,
col.`COLUMN_NAME` , '` like \'%' ,@q, '%\' UNION') AS q
from `tables` tbl
inner join `columns` col on tbl.`TABLE_NAME` = col.`TABLE_NAME`and col.DATA_TYPE='varchar'
where tbl.TABLE_SCHEMA = @t ;
Old post I know, but for others that find this via Google like I did, if you have phpmyadmin installed, it has a global search feature.
Source: Stackoverflow.com