January 5, 2010 at 4:20 pm
Hello World,
I'm trying to figure out if there is a way to query master or a dmo/dmv/etc to find SPs that use a certain view.
For instance, suppose I have an SP that is:
SELECT something FROM view1 WHERE something = 123
and I want to change that view1 to view2. Now I want to find all other stored procedures using view1, is there a quick way of finding those store procedures other than going to each one manually.
THANKS!
January 5, 2010 at 4:33 pm
quick and dirty way using dynamic SQL
select 'exec sp_helptext ', name from sys.objects where type = 'P'
run the results of that in another window, output to a file and then just search for the view name.
---------------------------------------------------------------------
January 5, 2010 at 4:40 pm
SQL Iron Chef (1/5/2010)
Hello World,I'm trying to figure out if there is a way to query master or a dmo/dmv/etc to find SPs that use a certain view.
For instance, suppose I have an SP that is:
SELECT something FROM view1 WHERE something = 123
and I want to change that view1 to view2. Now I want to find all other stored procedures using view1, is there a quick way of finding those store procedures other than going to each one manually.
THANKS!
I have a really bad habit of going to SQLServerCentral's Forums first before consulting Google (thats because SQLServerCentral is my favorite site).
I found my solution on Google.
SELECT o.name As "Stored Procedures"
FROM SYSOBJECTS o INNER JOIN SYSCOMMENTS c
ON o.id = c.id
WHERE c.text LIKE '%view1%'
AND o.xtype = 'P'
GROUP BY o.name
ORDER BY o.name
January 5, 2010 at 4:41 pm
george sibbald (1/5/2010)
quick and dirty way using dynamic SQLselect 'exec sp_helptext ', name from sys.objects where type = 'P'
run the results of that in another window, output to a file and then just search for the view name.
THNKS GEORGE!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply