Elnaz
Elnaz

Reputation: 2900

Find all stored procedures including a text in their body

I saw some answers about search text in stored procedures name, Id and etc. But I don't find out that what is the good way to find all stored procedures including the SQL of their body

More info: I have a table called X; I wand to drop it and I want to be sure that it has not been used in stored procedures.

Upvotes: 1

Views: 3157

Answers (1)

GuidoG
GuidoG

Reputation: 12059

this will find all procedures, functions, triggers, etc with the text Xin it

SELECT DISTINCT
       o.name AS Object_Name,
       o.type_desc,
       m.*
FROM   sys.sql_modules m
  INNER JOIN sys.objects o ON m.object_id = o.object_id
WHERE m.definition Like '%X%'

It is easy to alter so you only get procedures, or whatever you need

Upvotes: 1

Related Questions