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.

-- Warning: This script uses the undocumented stored
-- procedure sp_msforeachdb.  In the unlikely event that
-- this is removed from future versions of SQL Server,
-- the code below will no longer work.
exec sp_msforeachdb 'use ?;if exists (select id from ?.dbo.syscomments where text like ''%searchtext%'') select @@Servername as [Server],''?'' as [DB],object_name(id) as [Stored Proc],text from ?.dbo.syscomments where text like ''%searchtext%'''

Rate

5 (2)

Share

Share

Rate

5 (2)