Stored Procedures and the Tables Used by Them

Question

Is there a way to find out which tables are used by a single stored procedure by executing an SQL query?

Best regards, thanks for the help.

PS: I am using SQL Server 2005.

Answer 1

This article is about TechRepublic

Finding Dependencies in SQL Server 2005

describes a way to do this:

This tutorial will show how you can write a procedure that will look like all objects that depend on other objects.

Here is the code for creating a system stored procedure for finding object dependencies:

USE master
 GO
 CREATE PROCEDURE sp_FindDependencies
 (
         @ObjectName SYSNAME,
         @ObjectType VARCHAR(5) = NULL
 )
 AS
 BEGIN
     DECLARE @ObjectID AS BIGINT    

         SELECT TOP(1) @ObjectID = object_id
         FROM sys.objects
         WHERE name = @ObjectName
         AND type = ISNULL(@ObjectType, type)    

     SET NOCOUNT ON ;    

       WITH DependentObjectCTE (DependentObjectID, DependentObjectName, ReferencedObjectName, ReferencedObjectID)
         AS
         (
         SELECT DISTINCT
                sd.object_id,
                OBJECT_NAME(sd.object_id),
                ReferencedObject = OBJECT_NAME(sd.referenced_major_id),
                ReferencedObjectID = sd.referenced_major_id
         FROM    
                sys.sql_dependencies sd
                JOIN sys.objects so ON sd.referenced_major_id = so.object_id
         WHERE   
                sd.referenced_major_id = @ObjectID
         UNION ALL
         SELECT
                sd.object_id,
                OBJECT_NAME(sd.object_id),
                OBJECT_NAME(referenced_major_id),
                object_id
         FROM    
                sys.sql_dependencies sd
             JOIN DependentObjectCTE do ON sd.referenced_major_id = do.DependentObjectID       
         WHERE
                sd.referenced_major_id <> sd.object_id     
         )
         SELECT DISTINCT
                DependentObjectName
         FROM   
                DependentObjectCTE c
 END

(CTE) , , , . sys.sql_dependencies, .

Answer 2

sp_depends, , , .

Answer 3

sp_depends , proc.

Answer 4

, , SSMS "View Dependencies". , ...

Answer 5

, Dependancies; , , SQL Server 2000, depandancies . . sysComments, .

declare @crlfSearch varchar(max),@objectSearch varchar(max),@escapeSearch varchar(max)

set @crlfSearch=('%bid' + char(13)+'%')
set @objectSearch='%bid %'
set @escapeSearch ='%[[]Bid]%'
select distinct so.name
from syscomments sc
inner join sysobjects so
on sc.id=so.id
where text like @objectSearch or text like @crlfSearch
or text like @escapesearch

, , , , , ( , ), .