CODEWITHSUNDEEP
sqlsql-serverstored-proceduresssms
Chamila Maddumage
Chamila Maddumage

Reputation: 3876

How to find all Stored Procedures that referring particular Stored Procedure

I want to find all the list of stored procedures which is referring a given stored procedure. I am using SSMS 2017. Can anyone suggest a query for this?

Upvotes: 2

Views: 1637

Answers (4)

Voodoo
Voodoo

Reputation: 1658

SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%TableNameOrWhatever%'

Upvotes: 1

ARr0w
ARr0w

Reputation: 1731

SELECT Name
FROM sys.procedures
where name like '%someCommonNameOfProcedure%'

Upvotes: 0

Nikunj Satasiya
Nikunj Satasiya

Reputation: 1053

You should try this solution. For More information, you should visit this link.

SELECT 
    o.name AS ReferencingObject, 
    sd.referenced_entity_name AS ReferencedObject
FROM sys.sql_expression_dependencies  AS sd
INNER JOIN sys.objects AS o
    ON o.object_id = sd.referencing_id
WHERE sd.referenced_entity_name = 'my_object_name';

OR

SELECT  name AS ProcedureName ,
        CONVERT(VARCHAR(10), SysObj.modify_date, 103) AS [Create/Modify Date] ,
        CONVERT(VARCHAR(15), CAST(SysObj.modify_date AS TIME), 100) [Create/Modify Time]
FROM    sys.objects SysObj
WHERE   SysObj.type = 'P'
        AND DATEDIFF(D, SysObj.modify_date, GETDATE()) < YEAR(GETDATE())

OR

SELECT  name AS ProcedureName ,
        CONVERT(VARCHAR(10), SysObj.modify_date, 103) AS [Create/Modify Date] ,
        CONVERT(VARCHAR(15), CAST(SysObj.modify_date AS TIME), 100) [Create/Modify Time]
FROM    sys.objects SysObj
WHERE   SysObj.type = 'P'
ORDER BY SysObj.modify_date DESC

Upvotes: 0

Arulkumar
Arulkumar

Reputation: 13247

In the following query, in the WHERE clause, instead of '%YourProcedureName%', you can replace your procedure name, it will return the other procedures which has the given procedure name in it. 

-- Find specific word in the SPs
SELECT DISTINCT P.[name], M.[definition]
FROM sys.procedures P
JOIN sys.sql_modules M ON M.[object_id] = P.[object_id]
WHERE M.[definition] LIKE '%YourProcedureName%'

Upvotes: 2

Related Questions