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
End of Article
