SQL SERVER – Find Stored Procedure Related to Table in Database – Search in All Stored Procedure

Following code will help to find all the Stored Procedures (SP) which are related to one or more specific tables. sp_help and sp_depends does not always return accurate results.

----Option 1
SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%tablename%'
----Option 2
SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%tablename%'

Reference : Pinal Dave (http://www.SQLAuthority.com)

SQL Scripts, SQL Stored Procedure, SQL Utility
Previous Post
SQL SERVER – Cursor to Kill All Process in Database
Next Post
SQL SERVER – Fix : Error 14274: Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server. The job was not saved.

Related Posts

167 Comments. Leave new

  • Frankie Resto
    March 6, 2018 11:27 pm

    Thanks for all your help.

    Reply
  • Hi Pinal, Your options are pulling extra records where that table is NOT present. E.g. I was looking for Table “Device” and also we have Column “DeviceID” in other table. Your options were pulling records for DeviceID as well. I found below code more reliable. But you are the best judge.

    select distinct [Table Name] = o.Name, [Found In] = sp.Name, sp.type_desc
    from sys.objects o inner join sys.sql_expression_dependencies sd on o.object_id = sd.referenced_id
    inner join sys.objects sp on sd.referencing_id = sp.object_id
    and sp.type in (‘P’, ‘FN’)
    where o.name = ‘Device’
    order by sp.Name

    Reply
    • Your solution is using catalog view and would be better to use when dependencies in catalog view is correctly populated. I am querying raw text of code using syscomments.

      Reply
  • Boyapati Yamini Kumar
    May 17, 2018 12:49 pm

    Your solutions works, thank you so much, but i also have a requirement to pass multiple tables as combination and retrieve the Stored Procedures. Thanks in Advance!

    Reply
  • thank u so much , now is was working good

    Reply
  • RAGHU NANDIKOTKUR
    November 8, 2018 2:01 am

    use sp_depends

    Reply
  • Hi Pinal, why sp_depends is not reliable for this as you mentioned.

    Reply
  • I would like to know number of result set should return in specific stored procedure.
    Is it possible to find total resultset (Datatable) returning from StoredProcedure?

    Reply
  • This is fine. But the problem with the LIKE is that if I have a table named Employees and a table named EmployeesPhones, when I look for Employees I’m obtaining the SPs for both tables

    Reply

Leave a Reply