Search all Tables for a String
SQL Server 2000: Tested
SQL Server 2005: Tested
SQL Server 2008: Tested
SQL Server 2008R2: Tested
SQL Server 2012: Not Tested
Date: 2 Dec 2011
The last thing I ever want to do to a database is to have to search through all the text in it.
However, I've needed to do this 3 times now, and each time the reason has been a SQL Injection attack.
Despite the intent (malicious or mischievous), these injection attacks generally excel at injecting a text string (normally a URL) wherever they find a place.
Unless you are particularly lucky and have this occur to a non-production database (in which case one might question why your DEV environments are open to the world), the time to discovery usually means restoring to a previous copy of a production database is likely not to be an option due to the volume of changes that would need to be discarded.
This leaves repair as the only option, which means a long search through the database.
The nature of the search doesn't really lend itself to efficiency - looking at every row in every table is always going to hurt - so the code that I wrote to perform this task is not the prettiest or most efficient. However, it does get the job done, and records all occurrences of the string in an audit table.
I haven't included any code to remove the string, as this is very much dependant on the SQL Server, the database concerned and operational constraints.
I also created a reduced functionality stored procedure which only operates on a single table and does not record any results.
Stored Procedure to search all tables and table in which to record results:
Stored Procedure to search a single table (does not record results):