title: SQL Server: How to display lines which contains a specific text in stored procedures / functions / triggers / views?
body: <p>I am working with different versions of SQL Server: SQL Server 2008 R2 and SQL Server 2016. </p>
  
  <p>I want to search a specific text <code>employee_project</code> in all stored stored procedures / functions / triggers / views. </p>
  
  <p>So I wrote this query:</p>
  
  <pre><code>select *
  from sys.object so 
  inner join sys.sql_modules ssm on so.object_id = ssm.object_id 
  where ssm.definition like '%employee_project%'
  </code></pre>
  
  <p>The above query is working fine. </p>
  
  <p>Now I want to list only the lines where the specific text <code>employee_project</code> occur. </p>
  
  <p>For example suppose there is a stored procedure <code>ProcessProject</code> which has the specific text <code>employee_project</code> on 3 different lines this way:</p>
  
  <pre><code>select id from employee_project where name = @proj_name
  
  /* employee_project is validated */
  
  -- employee_project was updated
  </code></pre>
  
  <p>Then the new query should return the above 3 lines, along with the name of the concerned stored procedure / function / trigger / view. How can I do that?</p>
  
  <pre><code>object_name        line
  --------------------------------------------------------------------------
  ProcessProject     select id from employee_project where name = @proj_name
  ProcessProject     /* employee_project is validated */
  ProcessProject     -- employee_project was updated
  </code></pre>