Finding the text in the stored procedure in Sql server.

Vishalneeraj-24503
Posted by Vishalneeraj-24503 under Sql Server category on | Points: 40 | Views : 434
Write blow query:-

declare @newString varchar(205);
Set @newString = '%' + 'Search results' + '%'

select name from sysobjects where id in
(select id from syscomments where text like @newString)
order by name;

Comments or Responses

Posted by: Bandi on: 3/30/2015 Level:Platinum | Status: [Member] [MVP] | Points: 10
Hi Vishal,

This code snippet searches for the given text ( @newString) and retruns functions, CHECK/DEFAULT constraints, triggers, views and STORED PROCEDUREs.

So if we needed to search for only stored procedures TEXT, then there should be an extra condition

select name from sysobjects where id in

(select id from syscomments where text like @newString )
and xtype='P'
order by name;

Login to post response