Stored procedures and the tables used by them

Question

Hi people!

Is there a way to know what are the tables used by one stored procedure by doing an SQL query?

Best regards, and thanks for the help.

P.S.: I'm using SQL Server 2005.

Answer 1

Try sp_depends, although you should probably recompile the stored procedure to update the statistics in the database.

Answer 2

Look up sp_depends system stored proc.

Answer 3

I think that as long as the stored procedure and the tables are all in the same database then you can right click on the procedure in SSMS and click "View Dependencies". I don't know the query behind the dialog though...

Answer 4

This article on TechRepublic

Finding dependencies in SQL Server 2005

describes a way to do that:

This tutorial will show how you can write a procedure that will look up all of the objects that are dependent upon other objects.

Here is the code to create the 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

This procedure uses a Common Table Expression (CTE) with recursion to walk down the dependency chain to get to all of the objects that are dependent on the object passed into the procedure. The main source of data comes from the system view sys.sql_dependencies, which contains dependency information for all of your objects in the database.

Answer 5

As others indicated you can use the Dependancies stored procedures; however, in my experience and this was back on SQL Server 2000, the depandancies were not always reliable. In some cases they weren't being updated. You can always go to the sysComments table assuming your schema is not encrypted.

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

This query looks for three common cases you might have to add some but basically we find where the table name has a space after it, (This helps to limit cases where the table name is part of another table name), Has a return at the end of it, or is escaped within brackets.