Finding Dependency in Stored Procedure

Joydeep Das

Jul. 03, 15 · Interview

Likes (0)

Comment

Save

12.0K Views

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.

Dependency Object (computer science) dev VITAL (machine learning software) Implementation Diagram

Opinions expressed by DZone contributors are their own.

Related

Trending

Finding Dependency in Stored Procedure

Introduction

What We Want

How We Solve the Answer

Related

Partner Resources