Search a key word or Text in Sql server

It's quite often in real time require to search for a particular word or text  that used in stored procedure and function . There are couples of way getting dependency objects in sql server. But, this article follows bit different approach getting dependencies of the objects especially when a  table name or column is formatted in a string variable, for example ,see the below procedure where table name and column names are embedded in a string variable, In this situation we can't get the information of the object  where table  "Tab_Empolyee" is used  in sql server  not even with the built in  feature "View Dependencies".

CREATE PROCEDURE GetEmpolyeeDetails

AS

/* Modification History

---- Modifiedon  ChangesDescription      

      2014-12-31  New Procedure created

*/

BEGIN

DECLARE @SqlString VARCHAR(200)

SET @sqlString='select EmpID,EmployeeName from Tab_Employee'

EXEC(@sqlString);

END

Here, I would like to know list of procedures/Objects  that are  using table name "Tab_Employee" , for that I written a small query that accepts a key word or text  to search  and  returns the list of  objects  where that key word is used.

DECLARE @String varchar(50)='Tab_Employees'

DECLARE @Used TABLE (name VARCHAR(1000),Line VARCHAR(7000))

SELECT @String=REPLACE(REPLACE(REPLACE(@String,'%','[%]'),'_','[_]'),'-','[-]')

INSERT INTO @Used

      SELECT DISTINCT name,text FROM sysobjects o join syscomments c ON c.id = o.id

    WHERE text LIKE '%' + @String + '%'

    UNION

    

   SELECT 'ParentObject: '+Parent.name+ ' for: '+o.name,text FROM sysobjects o join syscomments c ON c.id = o.id

   JOIN syscolumns depend ON depend.cdefault=o.id

   JOIN sysobjects Parent ON Parent.id=depend.id

   WHERE text LIKE '%' + @String + '%'

  

SELECT * from @Used

from the above query it is  giving the result  where field  'name' represents object name like 'storedproc  or  func.

Well   you can also  find out  words or phrases that used in comments section.