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

  • Dean Giberson
    July 8, 2013 9:08 pm

    I found one situation where the information is not returned that is if you have a union all in the stored procedure. This is because (apparently) the second table/view is not considered in the dependency list. I assume this is because the driving data is the first table/view. Not sure if there is a way around this.

    Reply
  • Nice Query.. Thank You.

    Reply
  • Hi, Can you help me, how to find “inner Join or Cross Join” contained SPs in a Data base

    Reply
  • srikanth:

    Use the same code as in this thread except change the search criteria from a tablename to cross join and then inner join. However inner joins may be coded with just join so you may miss some. If you are looking for specific joins you are better off looking for the table name instead of or in addition to the join.

    Hope that helps,

    DG

    Reply
  • THANKS FOR WONDERFUL ANSWER

    Reply
  • Girijesh Pandey
    July 28, 2014 12:10 pm

    Hi Mshah,

    You can find dependant tables using sp_depends ‘TableName’

    Regards,
    Girijesh

    Reply
  • Hi i want to get table name and column names from particular stored procedure.
    eg: sp1 – used what table and columns in single select query

    Reply
  • Thanku..very much…

    Reply
  • how to find Vice versa of this

    Reply
  • how to find all the tables for a single stored procedure

    Reply
  • Thanks Pinal. Great article!

    Reply
  • Hi Pinal,

    This is the first time i am writing to this thread,

    I just need a small help from you.

    I need to get the list of tables used in a SQL Job, which may contains DTS Packages(So consider the tables which are been used in DTS too).

    Thanks in Advance.
    Abhinav

    Reply
  • I don’t think that dependency is store in any table.

    Reply
  • what is the error?

    Reply
  • Satheesh Davidson
    October 3, 2017 4:42 pm

    thanks boss

    Reply
  • The error I found is where a stored procedure is split across multiple rows in the SYSCOMMENTS table and the table we’re trying to find references to is where the split between SYSCOMMENTS rows is. There should be a way to concatenate the columns together where they relate to the same stored procedure.

    Reply
  • 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
  • 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

Leave a Reply