Script to find text in stored procedures

This is a simple script that when used by command-line OSQL/ISQL can help you locate a stored procedure.

How many times have you been looking for stored procedure code and could not find it?  This is a simple method to locate lost code.

When you manage many database servers this can become a common problem.  The scenario is familiar, you can recall working on a stored procedure, but not where it was located.

First the really easy part, create a file that contains each of your servers.  It should be a very basic text file like the following:
(dbservers.txt)
Server1
Server2
Server3

Create your script using the text in the script box and save it as a .sql file.  For this example, I used sqlfile.sql.  Please remove the comments first, they are not needed.

After saving the T-SQL script, open a command-line window (DOS Command Prompt) and type the following at the prompt:

FOR /F %s in (dbservers.txt) DO @OSQL -S%s -E -i sqlfile.sql >> search.txt

This will loop through all database servers in your file and search for the text you provide as "searchtext".

When you manage many SQL Servers this is a very handy tool to use.  Keep in mind you can use this same method to search for job steps, table names, views, functions, etc.