CODEWITHSUNDEEP
sql-serverstored-procedures
user2530748
user2530748

Reputation: 260

How To Find all Stored Procedure that are using a specific function

Hello is there a way that I can easily find all stored procedure in SQL Server that are using a specific function like for ex. function fn_Test()?

I want to see all the stored procedures using this function.

Thanks

Upvotes: 1

Views: 2445

Answers (4)

Ben Thul
Ben Thul

Reputation: 32737

Take a look at sys.dm_sql_referencing_entities.

Upvotes: 0

user2728841
user2728841

Reputation: 1427

This snippet of sql will list all the stored procedures that contain the name of the function. However that could include content in comments. I think this would be adequate for the job though

select distinct name from sysobjects
where type = 'P'
and id in (select id from syscomments where text like '%fn_Test%')
order by name

Upvotes: 0

Simon1979
Simon1979

Reputation: 2108

This should do what you are after, it will return any sproc with the entered text within it somewhere:

SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%fn_Test%'

Upvotes: 3

BAdmin
BAdmin

Reputation: 937

You can use,

SELECT obj.Name SPName, sc.TEXT SPText
FROM sys.syscomments sc
INNER JOIN sys.objects obj ON sc.Id = obj.OBJECT_ID
WHERE sc.TEXT LIKE '%' + 'YOUR_FUNCTION_NAME' + '%'
AND TYPE = 'P'

Upvotes: 2

Related Questions