locked
find all the objects that is used in stored procedure in sql server 2008 RRS feed

  • Question

  • Hi,

    I need to find all the list of tables, views, functions etc that exists in a procedure. If there is a view used in the procedure then it should also list down the view name and also the tables used in the view. I am using the below querywhich i got by googling, But it is not working as expected.

     ;WITH stored_procedures AS (
    SELECT 
    o.name AS proc_name, oo.name AS table_name,
    ROW_NUMBER() OVER(partition by o.name,oo.name ORDER BY o.name,oo.name) AS row
    FROM sysdepends d 
    INNER JOIN sysobjects o ON o.id=d.id
    INNER JOIN sysobjects oo ON oo.id=d.depid
    WHERE o.xtype = 'P')
    SELECT proc_name, table_name FROM stored_procedures
    WHERE  proc_name = 'proc_name'
    ORDER BY proc_name,table_name


    Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.

    Thursday, April 4, 2013 5:06 PM

Answers

  • Have a play with this...

    SELECT objects.name As suspected_dependencies
    FROM   sys.procedures
     INNER
      JOIN sys.all_sql_modules
        ON all_sql_modules.object_id = procedures.object_id
     LEFT
      JOIN sys.objects
        ON objects.name <> procedures.name
       AND all_sql_modules.definition LIKE '%' + objects.name + '%'
    WHERE  procedures.name = 'sp_alterdiagram'


    George
    blog | twitter

    Friday, April 5, 2013 11:30 AM
    Answerer

All replies

  • hi,

    right click on the object explorer item, choose 'dependencies', select 'objects on which <your object> depends'

    and bob's your uncle.


    Regards, Nico

    pdfaid, my blog

    Thursday, April 4, 2013 5:11 PM
  • View dependencies are not showing accurate data. It is missing some views or tables at times. I manually checked all the procedures and found out the objects involved. Killed lot of time :(

    Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.

    Friday, April 5, 2013 11:23 AM
  • Have a play with this...

    SELECT objects.name As suspected_dependencies
    FROM   sys.procedures
     INNER
      JOIN sys.all_sql_modules
        ON all_sql_modules.object_id = procedures.object_id
     LEFT
      JOIN sys.objects
        ON objects.name <> procedures.name
       AND all_sql_modules.definition LIKE '%' + objects.name + '%'
    WHERE  procedures.name = 'sp_alterdiagram'


    George
    blog | twitter

    Friday, April 5, 2013 11:30 AM
    Answerer
  • Thanks
    Tuesday, October 15, 2013 7:00 AM
  • Perfect answer.

    Thanks.

    Wednesday, August 10, 2016 3:00 AM
  • Hey, its working but the result displaying commented tables also. Is there any other query which will avoid commented tables used in stored procedure? 
    Friday, September 8, 2017 1:24 PM