Post reply

Query to check the tables part of procedures

  • July 12, 2017 at 3:58 pm

    #402280

    Hi,

    Though sp_depends 'proc_name' displays all the tables which are part of the procedure but if the tables are part of dynamic query it never appears. Is there anyway to get the details?

    I mostly use this query

    SELECT DISTINCT p.name AS proc_name, t.name AS table_name
    FROM sys.sql_dependencies d
    INNER JOIN sys.procedures p ON p.object_id = d.object_id
    INNER JOIN sys.tables  t ON t.object_id = d.referenced_major_id
    where p.name = 'proc_name'
    ORDER BY proc_name, table_name

    Thanks.

  • July 12, 2017 at 8:18 pm

    #1950667

    If you have dynamic SQL in play there is no way to know what tables were affected, at least with any certainty. A grep of the plan cache would get you most of them. But trivial ones and ones that flushed out before you read them and any with OPTION (RECOMPILE) would never be seen.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

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

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