I am trying to puzzle out where to find all occurrences of the url inside a database. I wish to search all tables and all sorts of fields. However I have no clue how to start or maybe it's even possible.

An easy solution could be doing something similar to this:

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

You'll be able to just perform a find on myfile.sql for that URL you would like.

Old publish I understand, however for others that find this via Google like Used to do, for those who have phpmyadmin installed, it features a global search feature.

Brute pressure method

declare @url varchar(255)

set @url = 'stackoverflow.com'

select 'select * from ' + rtrim(tbl.name) + ' where ' + 
          rtrim(col.name) + ' like %' + rtrim(@url) + '%'
from sysobjects tbl
inner join syscolumns col on tbl.id = col.id 
and col.xtype in (167, 175, 231, 239) -- (n)char and (n)varchar, there may be others to include
and col.length > 30 -- arbitrary min length into which you might store a URL
where tbl.type = 'U'    -- user defined table

This can produce a script you could execute around the database.

select * from table1 where col1 like '%stackoverflow.com%'
select * from table1 where col2 like '%stackoverflow.com%'
select * from table2 where col3 like '%stackoverflow.com%'


No elegant solution, however, you could achieve it having 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 can most likely speed this up by checking which posts contain strings while building your column array, and through mixing all of the posts per table in a single giant, comma-separated WHERE clause.

Scott gives among how to get it done, but now you ask , why would you need to? If you want to perform a find-and-replace on the specific string, you might try carrying out a mysqldump of the database, perform a find-and-replace within an editor, then re-load the database.

Maybe were you to give some background on what you're attempting to achieve, others might have the ability to have better solutions.

I had been searching for exactly the same but could not think it is, and so i create a small script in PHP, you'll find it at: http://tequilaphp.wordpress.com/2010/07/05/searching-strings-in-a-database-and-files/

Best of luck! (I remove some private code, tell me basically did not break it along the way :D)