title: Programmatically identify what SQL Server stored procedures are actually using a specific parameter
body: <p>I have a SQL Server database (it's 2012 but could be 2008) with thousands of stored procedures.  </p>
  
  <p>I would like to programmatically know which stored procedure are receiving a specific parameter <code>companyid</code> but are NOT really  using it inside their queries.</p>
  
  <p>So the 2 goals of this project are:  </p>
  
  <ol>
  <li>Identify what stored procedures do NOT receive the parameter <code>companyid</code>  </li>
  <li>Identify those stored procedures that DO expect the parameter <code>companyid</code> but that are not really using it within their code</li>
  </ol>
  
  <p>I am envisioning to either write some TSQL or write an app in C# in order to complete the task - either suggestion would work.</p>
  
  <p>So I'm hoping to hear from you guys what would be the best way to achieve so?  </p>
  
  <p>Thanks in advance</p>
  
  <p>==========================================================<br>
  BASED ON BOTH SUGGESTIONS, THE SOLUTION IS THE FOLLOWING:  </p>
  
  <pre><code>SELECT  Specific_Name,  
          ((len(ROUTINE_DEFINITION) - len(replace(ROUTINE_DEFINITION, '@companyid', '')))/ 10) as COUNT,  
      ROUTINE_DEFINITION  
  FROM    INFORMATION_SCHEMA.ROUTINES  
  WHERE   ROUTINE_DEFINITION LIKE '%@companyid%'  
  ORDER BY 2, 1
  </code></pre>
  
  <p>Note: dividing by 10 the string replacement will give you the count of instances of that specific string within the body. 1 means it's only the parameter, 2 or more means it's used in the body.</p>
  
  <p>Thank you both !!</p>