Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2009
    Posts
    1

    Unanswered: Stored Procedure listing all views and underlying views

    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.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    USE playdb2
    GO
    
    CREATE VIEW dbo.view1
    AS
      SELECT 'view1' As [view1]
    GO
    
    CREATE VIEW dbo.view2
    AS
      SELECT 'view2' As [view2]
    GO
    
    CREATE VIEW dbo.view3
    AS
      SELECT view1.view1
           , view2.view2
      FROM   dbo.view1
       CROSS
        JOIN dbo.view2
    GO
    
    SELECT v.name As [view]
         , x.name As [underlying_view]
    FROM   sys.views v
     INNER
      JOIN syscomments c
        ON v.object_id = c.id
     LEFT
      JOIN sys.views x
        ON c.text LIKE '%' + x.name + '%'
       AND x.name <> v.name
    
    GO
    DROP VIEW dbo.view3
    DROP VIEW dbo.view2
    DROP VIEW dbo.view1
    Obviously, if there is a view called "dbo.view33", this will give you spurious results because we're doing wildcard matches
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2002
    Posts
    272
    Or something like

    select o.name, o.type_desc, o2.name, o2.type_desc
    from sys.sysdepends sd
    join sys.objects o on sd.id = o.object_id
    join sys.objects o2 on sd.depid = o2.object_id

    sys.sysdepends holds all dependecies, so there's no need to join on (partial) object names like George does.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •