MSSQL Tutorial: Search code inside of stored proceedures

DECLARE @SEARCHSTRING VARCHAR(255), @notcontain Varchar(255)

SELECT @SEARCHSTRING = ‘expiration_date =’, @notcontain = ”

SELECT DISTINCT sysobjects.name AS [Object Name] ,
case
when sysobjects.xtype = ‘P’ then ‘Stored Proc’
when sysobjects.xtype = ‘TF’ then ‘Function’
when sysobjects.xtype = ‘TR’ then ‘Trigger’
end as [Object Type]

FROM sysobjects,syscomments

WHERE sysobjects.id = syscomments.id AND sysobjects.type in (‘P’,’TF’,’TR’) AND sysobjects.category = 0 AND CHARINDEX(@SEARCHSTRING,syscomments.text)>0 AND ((CHARINDEX(@notcontain,syscomments.text)=0 or CHARINDEX(@notcontain,syscomments.text)<>0))

Sorry, this little piece of handiwork isn’t my own. I got it from select-sql.com but I thought I would share the love. You can use this to search through the code inside of your stored proceedures to find whatever it is you’re looking for.

You may also like...

Leave a Reply