How do I search across all columns in all tables in SQL?

The development practice at Webapper does a fair bit of "version 2" system development. Our customer has an existing application, built maybe in ColdFusion, maybe in PHP or another scripting language. Maybe in Access forms, maybe as a desktop application. We'll use it as a prototype for creating a new webified application.

One part of these jobs is tracing how the old application works. Since there is never any documentation, and the source code is often too scary to look at (or no longer even available), it can be hard to find out the business rules underlying the software.

I'm working on such a project these days, and needed to find out where a particular string is being stored in the old database. The database is not normalized, there are no foreign key constraints, and there are inconsistent key names (like users.user_id and records.u_id as pkey and fkey, respectively).

I needed to find where in the database a particular string was being stored. I hunted in the most obvious tables and columns, but there are about 100 tables, and about 500 megs of data (lots of rows). I needed a SQL script to hunt down a particular keyword.

Google found Doug Hughes's stored proc, which I remember having used with success in the past, but today it threw a ton of errors and came up empty handed. A bit more Googling turned up Vyas's better stored proc. It worked like a charm. Hope this helps you!