Searching all tables in a database

In my recent travels, I've had to do a bit of discovery work.  Specifically, if we move a DB from one server to another, what will break, and how?

SQL Profiler is great at telling me who's doing what with a particular DB.  With it, I can figure out which SQL clients need their connection strings modified.  Great news!  But here's an interesting twist.  In all the Profiles I captured, I noticed that some of the UPDATE and INSERT statements contained references to UNC paths.  Wouldn't you know it, the paths refer to the DB server that's being decommissioned.

So even if we successfully moved the DB and reconfigured its clients, the application would still break because it would query the DB and get a path that didn't exist anymore.  Well, that's nice to know.  But I wanted to know if there was anything else, anywhere, within the DB that made reference to that server.

I found a most excellent procedure here:–1#2  The code trawls through and returns the table and column names that contain the string you're looking for.  It's enough to point you in the right direction.

If you don't feel like trawling through the entire post, here's the relevant TSQL  Call it by running a command like: