Finding Dependency In Stored Procedure

Introduction

Here in this article we are trying to discuss about the finding reference object within stored procedure and also finding the calling procedure references. Hope you like it and it will be informative.

What we Want

Developers are writing several stored procedure almost every day. Sometimes developers need to know about the information such as what object is used within the stored procedure or from where (SP) the specified stored procedure call. This is the vital information for the developer before working on a particular stored procedure.

Here we are representing a pictorial diagram to understand the nature of implementation.

Now we have to answer some question

1.    What are the DB Object used in Stored Procedure1 and there type.

2.    In case of Store Procedure3 which procedure calls the Store Procedure3

So we are not going to read the Stored Procedure to find the answer. Suppose the each procedure have more than 3000 line.

How we solve the answer

To solve the answer first we take the example and create an example scenario to understand it.

-- Base Table

CREATE TABLE T1

          (EMPID  INT, EMPNAME  VARCHAR(50));

GO

 CREATE TABLE T2

          (EMPID  INT, EMPNAME  VARCHAR(50));

GO

--1

CREATE PROCEDURE [dbo].[Procedure1]

AS

BEGIN

    SELECT * FROM T1;

      SELECT * FROM T2;

      EXEC [dbo].[Procedure3];

END

GO

--2

CREATE PROCEDURE [dbo].[Procedure2]

AS

BEGIN

    EXEC [dbo].[Procedure3];

END

GO

--3

CREATE PROCEDURE [dbo].[Procedure3]

AS

BEGIN

    SELECT * FROM T1;

END

GO

Now we are going to solve the question

What are the DB Object used in Stored Procedure1 and there type.

sp_depends Procedure1

In case of Store Procedure3 which procedure calls the Store Procedure3

SELECT OBJECT_NAME(id) AS [Calling SP]

FROM   syscomments

WHERE  [text] LIKE '%Procedure3%'

GROUP BY OBJECT_NAME(id);

Hope you like it.

Posted by: MR. JOYDEEP DAS