MSSQL Tutorial: Search code inside of stored proceedures

Leave a comment Standard

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.

Advertisements

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