Find Any String in Any SQL Code in Any Database

Have you ever needed to quickly find all the stored procedures that contain a certain field? Have you ever needed to find some code you wrote a while ago but have forgotten the name of that code? Have you ever needed to find all the code on the server that references a particular table? To find such items, you could purchase database documentation software—or you could use Find.sql.

Find.sql is the most popular piece of code I’ve ever written. This stored procedure finds any string in any type of SQL code (e.g., view, procedure, function) in any database on a server without hurting the server’s overall performance. Find.sql won’t search for strings in the database’s data, however. If you need that capability, check out the table-searching script at www.users.drew.edu/skass/sql/SearchAllTables.sql.txt.

I named the stored procedure Find so its name is easy to remember. Find.sql is also easy to use. You just need to download the stored procedure by clicking the 99466.zip hotlink above. On the server you want to search, you call the stored procedure by typing

 Find 'mystring'

where mystring is the string you want to search for. The stored procedure will search for that string in all the databases on your server and return a list of the objects that contain it. As Figure 1 shows, the list is ordered by database. It tells you the name of the object as well as the type of object in which your string has been found. It uses notations such as P for procedure, FN for function, and V for view for the object type.

I wrote Find.sql for SQL Server 2000, but it should work on SQL Server 6.5 through SQL Server 2008. Many DBAs and developers are using it to make their jobs easier. Play around with Find.sql to see what it can do—it could save you hours of frustration when you need to find a specific piece of code.

—Michael Berry, senior DBA, Ohio Public Employees Retirement System