CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
SF Developer
SF Developer

Reputation: 5384

Programmatically identify what SQL Server stored procedures are actually using a specific parameter

I have a SQL Server database (it's 2012 but could be 2008) with thousands of stored procedures.

I would like to programmatically know which stored procedure are receiving a specific parameter companyid but are NOT really using it inside their queries.

So the 2 goals of this project are:

  1. Identify what stored procedures do NOT receive the parameter companyid
  2. Identify those stored procedures that DO expect the parameter companyid but that are not really using it within their code

I am envisioning to either write some TSQL or write an app in C# in order to complete the task - either suggestion would work.

So I'm hoping to hear from you guys what would be the best way to achieve so?

Thanks in advance

==========================================================
BASED ON BOTH SUGGESTIONS, THE SOLUTION IS THE FOLLOWING: 

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

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.

Thank you both !!

Upvotes: 0

Views: 114

Answers (2)

Sanj
Sanj

Reputation: 3870

I could make it sound simple.

Look at ROUTINE_DEFINITION field of 

select * from INFORMATION_SCHEMA.ROUTINES

This basically gives u the body of the sp, fn, vw.

Upvotes: 1

Sachin
Sachin

Reputation: 40970

Your first goal may be achieved with this

SELECT proce.name FROM sys.procedures proce 
INNER JOIN sys.procedures params ON proce .object_id=params.object_id
WHERE params.name='@companyid'

For second goal you can try this

SELECT proce.name from sys.procedures proce 
INNER JOIN sys.sql_modules modules on proce.object_id=modules.object_id
WHERE definition like '%@companyid%'

Upvotes: 1

Related Questions