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?
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
-
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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply