Post reply

Objects used in a procedure

  • May 27, 2009 at 2:55 am

    #99699

    How to find out all the objects used in a procedure with their type of use.

    I know that all the objects used can be found out using sysdepends, but is there a way to find out if each of those tables is modified, created, selected(read) or inserted etc??

    Also, can the same procedure be extended to dynamic SQL in any way?

    Is the compiled form of procedure available in any of the system tables? or can be obtained in any other way?

  • May 27, 2009 at 8:48 am

    #1000689

    I'm pretty sure you'd have to search for the text (insert, update, etc) in the source code of the stored procedures which is stored in syscomments.

    This article describes queries that can be used to search syscomments for different versions of SQL Server: http://www.sqlservercentral.com/articles/Administration/2792/.

    Greg

  • May 27, 2009 at 11:47 pm

    #1001220

    Thanks Greg for your reply..

    But is there any way to avoid the comments that could have been added in the procedure, because the text in the syscomments table contains the comments also.

    In addition, I can search for the select, update, delete etc but how to relate these exactly to the objects used in these statements? How to identify them?

    Thanks in advance.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply