Find references in all stored procedures on server (2005+)

Imagine you have some text in a stored procedure somewhere and you can’t remember where it is or maybe it is in numerous places and you need a list of all the DBs and objects that contain it. This will help you find all the instances of your text.

One place this is useful is if you write a comment like “–TODO” where you need to make an amend (but are waiting on someone before you can make the change). Another example is your name or a reference to a table within a dynamic sql statement which is not picked up by dependencies. Anyway, I am sure you get the drift. You want to search all stored procedures for a string. There are some nice visual tools you can download to do this but if you cannot do this for some reason then here’s a useful script using an undocumented sp by MS – sp_MSforeachdb

Not surprisingly MSforeachdb carries out a command on every database on the server. This command uses the ? marker to indicate the current DB.

Stored procedures are made visible via the sys.sql_modules view. (http://msdn.microsoft.com/en-us/library/ms175081.aspx)

Combining these two gives you a powerful and flexible search mechanism.

-- David Bridge
-- David Bridge Technology Limited
-- February 2011
DECLARE @SearchArg varchar(200) = 'TODO'
CREATE TABLE #temp (DB_name varchar(128), Object_name varchar(128));
DECLARE @sql varchar(1000) = 'USE [?];
INSERT INTO #temp
SELECT	 db_name() as db_name
,object_name(object_id) as object_name
FROM sys.sql_modules
WHERE definition like ''%@SearchArg%'''
SET @sql = REPLACE (@sql,'@SearchArg',@SearchArg)
exec sp_MSforeachdb @sql

SELECT * FROM #temp
DROP TABLE #temp
Advertisements

About davidbridge

I am a contractor for David Bridge Technology Limited specialising in database design and Web development using Microsoft technologies such as c#, MVC .net and SQL Server (full stack)
This entry was posted in Developer stuff, SQL Stuff and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s