April 21, 2004 at 6:48 am
Hi,
I want to get a list of all views that are dependent on a given view. I've noticed that if some of the metadata for a given view changes (e.g. View2 selects from View 1 and View 1 is then dropped and recreated) then it is possible that sp_depends will not return the required info.
Witness the following:
============================================
use tempdb
GO
create table MyTable (col1 int)
GO
create view MyView1 as select * from MyTable
GO
create view MyView2 as select * from MyView1
GO
sp_refreshview MyView2
GO
sp_refreshview MyView1
GO
sp_depends MyView1 --This will NOT return MyView2 as a dependent object
GO
sp_refreshview MyView1
GO
sp_refreshview MyView2
GO
sp_depends MyView1 --This WILL return MyView2 as a dependent object
GO
==========================================
I was going to simply loop all over all views in a DB and call sp_refreshview on them all before calling sp_depends on the object that I'm interested in but evidently this won't work because, as we can see from the example above, the order in which you call sp_refreshview on the views is significant.
So in short, how can I call "sp_depends <objectname>" and be absolutely sure that what I get back is a definitive list of all dependent objects.
Any help would be VERY much appreciated.
Regards, Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
April 21, 2004 at 7:46 am
I always search syscomments...
Select distinct so.type,so.name
FROM syscomments sc
JOIN Sysobjects so
on so.id = sc.id
and sc.text like '%MyView1%'
April 21, 2004 at 8:03 am
Works a treat. Thanks AH.
Still annoying about sp_depends though!!!
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply