Find dependencies on a table

  • 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 1 month, 3 weeks ago by  water490.
  • 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;

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

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