Post reply

Find dependencies on a table

  • February 26, 2024 at 5:21 am

    #4384809

    I am in the process of doing a re-write for my project so I need to find all SP that use a particular table.  SS has a tool that lets me do this.  How reliable is it?

    Screenshot 2024-02-25 211820

    I tried sp_depends and compared the output from it and the above approach...they don't give me the same results.  sp_depends was actually wrong b/c it missed a few SP that referenced the table I am looking for.

    Would a query be more reliable?  If yes, what would the query look like?

    Thank you

    • This topic was modified 5 months ago by  water490.

  • February 26, 2024 at 8:16 am

    #4384887

    I use the following script to search for dependencies

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    DECLARE 
        @ObjectSchema SYSNAME = '' /* You can limit the schemas to return here.  EMpty/NULL will return all schemas */
      , @ObjectName   SYSNAME = 'PartialObjectName' /* Add your table name here */

    SELECT
          ObjectType    = O.type_desc
        , SchemaName    = SCHEMA_NAME(O.schema_id)
        , ObjectName    = O.name
        , ObjectDef     = OBJECT_DEFINITION(O.[object_id])
    FROM sys.objects AS O
    INNER JOIN sys.sql_modules AS S
        ON S.[object_id] = O.[object_id]
    WHERE o.type_desc not in ('INTERNAL_TABLE','SERVICE_QUEUE','SYSTEM_TABLE')
      AND SCHEMA_NAME(O.schema_id) = ISNULL(NULLIF(@ObjectSchema, ''), SCHEMA_NAME(O.schema_id))
      AND O.name NOT LIKE 'syncobj%'
      AND PATINDEX('%' + @ObjectName + '%', S.[definition]) > 0
    GROUP BY O.type_desc, O.schema_id, O.name, O.[object_id]
    ORDER BY 1, 2, 3;

    How to post data/code on a forum to get the best help.

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

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