Biz

Ever needed to search for a string in your stored procedures?

I was digging through an older database I am rewriting and I stumbled on this little stored procedure.  It uses the internals of SQL Server to search the stored procedures for a provided string.  Just one of those bits of code we are all meaning to write but never do.

CREATE PROCEDURE Find_Text_In_SP
@StringToSearch varchar(100) 
AS 
   SET @StringToSearch = '%' +@StringToSearch + '%'
   SELECT Distinct SO.Name
   FROM sysobjects SO (NOLOCK)
   INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
   AND SO.Type = 'P'
   AND SC.Text LIKE @stringtosearch
   ORDER BY SO.Name
GO

I'm not even sure who wrote it but it seems like something handy to have around.

Comments are closed
Mastodon