Reputation: 2900
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
Reputation: 12059
this will find all procedures, functions, triggers, etc with the text X
in 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