Searching SQL Server for a word or phrase across all objects

So you know what the column is called, or what the table is called, but you just don't know where to find it on your SQL Server? This is my solution for searching throughout SQL Server for a particular string, it will run on 2005, 2008R2 and 2012.

First we need to define some variable and establish where we are going to look. The below code gets the variables we need created, and defines a cursor that will gather the names of all the databases on the server.
Then we need to cycle through these databases and do our search on each one. The following dynamic SQL iterates through the databases and performs the search on each.



And there you have it, but how can we improve on this? by eliminating the cursor? by using a table (temporary, table variable or CTE) to store our result set? Yes of course, but this works and will get you on your way. As an extra result, lets add a search that checks SQL Agent jobs as well, see below for that code:



So now you will get many different result sets, and if you have more than 100 databases on your server you will exceed the maximum result sets that SSMS will display in 2008R2, so what to do? Lets store the results in a table and display them with a SELECT statement, by doing this we can also eliminate the empty result sets. Working fine? If you're getting access errors one small change will address that, lets only look in the databases you actually have access to by adding this:



So this is all you need to search the text of procedures, views, triggers etc and the names of all tables and columns for a specific string. It works and you can use it now, BUT, there are major improvements to be made...next post will cover converting it to a stored procedure and using parameters, as well as better filtering of the output.

Here is the final version, with the temporary table and all other changes added:



PART 2 - Improvements