SQL2005: Searching across all databases

~J~ · 22 Jul 2008 at 10:40

Anyone know of a stored procedure or piece of script that will allow me to search across EVERY database and the tables within it, for a simple bit of text?

We've just migrated about 100 databases onto a server, each database has about 50-60 tables, each table has about 30-40 fields.

These database date back from last week to 2002 and we've just discovered that back in 2004 there was a SQL injection and need to remove the offended 'crap'.

Just a dead easy bit of text, but want to go through EVERYTHING to see where it is!

Any thoughts?

GravyMonster · 22 Jul 2008 at 10:55

When I had to clear out some old crap from a DB that had been injected I used this - although it only works at table column level, not database level so may not be entirely what you're after, but I can guarantee it works well.

~J~ · 23 Jul 2008 at 10:31

Thanks, that actually worked a treat! Quite quick too which was surprising.

GravyMonster · 23 Jul 2008 at 11:23

Glad I could help

It's a really good tool - it saves writing lots of SQL and gets around the 4,000 character limit on the native replace function which can really ruin your day when you've got lots of text fields to deal with.