Post reply

Stored Procedure / SQL Query listing all views and underlying views

  • January 6, 2009 at 4:11 am

    #71697

    Hello,

    I need to list out all views in my SQL Server database in Column 1 and all underlying views in Column 2, group by Column 1, Column 2.

    For e.g.

    View A uses Views B, C

    View B uses Views E, F

    View E uses View G

    View F has no underlying view (only tables)

    View G has no underlying view (only tables)

    View C uses View D

    View D has no underlying view (only tables)

    Desired Output:

    Col1 Col2

    ---- ----

    A --- B

    A --- C

    B --- E

    B --- F

    C --- D

    D --- NULL

    E --- G

    F --- NULL

    G --- NULL

    I have been trying to use sys tables to have this done, but am somehow not able to. I would be really obliged if anybody could provide me with some help.

    Thanks in advance.

    Cheers.

  • January 6, 2009 at 4:50 pm

    #922266

    I can't remember if this works prior to 2005, but give this a try.

    select distinct s1.name as MainView, s2.Name as ViewUsed

    from sysdepends d

    join sysobjects s1 on s1.id = d.id

    join sysobjects s2 on s2.id = d.depid

    where s1.xtype = 'V' and s2.xtype = 'V'

    This will only show those views that are dependent upon (reference) other views. But if the tables work for you, it can be rewritten to start FROM sysobjects and left join to sysdepends.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply