[ Home  |  FAQ-Related Q&As  |  General Q&As  |  Answered Questions ]

    Search the Q&A Archives

I am creating a stored proc to create a report of dependenci...

<< Back to: Sybase FAQ: 9/19 - ASE Admin (6 of 7)

Question by RR
Submitted on 10/23/2003
Related FAQ: Sybase FAQ: 9/19 - ASE Admin (6 of 7)
Rating: Not yet rated Rate this question: Vote
I am creating a stored proc to create a report of dependencies.  We are creating views which are using other views in its creation.  I want to list views with all the child views and traverse this tree until I find no other dependencies.

Ex:
Parent   Child  'Child's child'
----------------------------------
pv       cv      ccv             ......
----------------------------------

I think it may not be a good idea to have multiple level of dependency for views, but I would like to keep this stored proc generic so that it can traverse multiple levels to find out the dependency of views...

here is my stored proc:

Drop Procedure sp_viewlist
go
Create Procedure sp_viewlist as
Begin
Declare @vwname varchar(50)
Declare @depvwname  varchar(50)
Declare @parentid int
Declare @childid int

create table #tempViewList (ParentView varchar(50), ChildView varchar(50))

Declare c_ViewList cursor for
select o.id,sd.depid
from sysobjects o,sysdepends sd
where o.type = 'V'
and o.id = sd.id
and sd.depid in ( select id
         from sysobjects
         where type='V'
          )

open c_ViewList
fetch c_ViewList into @parentid, @childid
while (@@sqlstatus = 0)
Begin
  select @vwname=name from sysobjects where id = @parentid
  select @depvwname=name from sysobjects where id = @childid
  insert into #tempViewList values (@vwname,@depvwname)
  fetch c_ViewList into @parentid, @childid
End

select ParentView,ChildView
from #tempViewList
Group By ParentView,ChildView
Order By ParentView,ChildView

End
go

thanks in advance.


Answer by fhj
Submitted on 5/18/2007
Rating: Not yet rated Rate this answer: Vote
n,nm, ugh

 

Your answer will be published for anyone to see and rate.  Your answer will not be displayed immediately.  If you'd like to get expert points and benefit from positive ratings, please create a new account or login into an existing account below.


Your name or nickname:
If you'd like to create a new account or access your existing account, put in your password here:
Your answer:

FAQS.ORG reserves the right to edit your answer as to improve its clarity.  By submitting your answer you authorize FAQS.ORG to publish your answer on the WWW without any restrictions. You agree to hold harmless and indemnify FAQS.ORG against any claims, costs, or damages resulting from publishing your answer.

 

FAQS.ORG makes no guarantees as to the accuracy of the posts. Each post is the personal opinion of the poster. These posts are not intended to substitute for medical, tax, legal, investment, accounting, or other professional advice. FAQS.ORG does not endorse any opinion or any product or service mentioned mentioned in these posts.

 

<< Back to: Sybase FAQ: 9/19 - ASE Admin (6 of 7)

[ Home  |  FAQ-Related Q&As  |  General Q&As  |  Answered Questions ]
© 2008 FAQS.ORG. All rights reserved.