Thursday, 2 July 2015

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