how to find SP Reference

Question

I want to know whether one SP is referenced anywhere. Currently I am checking using SP_DEPENDS.

Is there any other way to check this...?

Answer 1

Try:

SELECT OBJECT_NAME(m.object_id), m.*
  FROM SYS.SQL_MODULES m
 WHERE m.definition LIKE N'%my_sp_name%'

Mind that SYSCOMMENTS and INFORMATION_SCHEMA.ROUTINES have nvarchar(4000) columns. So if my_sp_name is used at position 3998, it won't be found. SYSCOMMENTS does have multiple lines but ROUTINES truncates.

Reference: Listing SQL Server Object Dependencies

Answer 2

If your dependencies are broken, the only way I am thinking now to be confident is to export a SQL file with all the stored procedure definitions and then search inside the .sql file for dependencies using your favorite source code editor.

Answer 3

I wrote this query to search "code objects" (which includes procedures, functions, triggers, views, and whatever else gets parked in sys.sql_modules) for the selected string, whether table, column, comment, or stored procedure name. I am reasonably certain that this will account for the possibility of the string being "split" across separate data pages.

The columns returned are:

• Owning schema
• Object name
• Object type (as per type_desc in sys.objects)
• Code lenght in bytes (sometimes helps to know how big the code is)
• Actual code containing the string

This will only list objects where the string is present. If you search for, say "Declare", you'll probably get every procedure and function in the database. It also does not evaluate how the string is used. If your procedure name is present (only) within a comment in another procedure, that procedure will be listed.

In short, it's a starting point. It will list where your string exists; you then have to go in and review each case, to determine how it is used.

DECLARE @SearchText   varchar(100)

SET @SearchText = 'YourTextHere'

SELECT
   schema_name(ob.schema_id)  SchemaName
  ,ob.name
  ,ob.type_desc
  ,len(mo.definition) CodeLength
  ,mo.definition
 from sys.sql_modules mo
  inner join .sys.objects ob
   on ob.object_id = mo.object_id
 where mo.definition like '%' + @SearchText + '%'
 order by
   case schema_name(ob.schema_id)
     when 'dbo' then 'A'
     else 'B' + str(ob.schema_id, 10)
   end
  ,ob.type_desc
  ,ob.name