Display entire cascading query structure

[rich.barry]

Is anyone aware of an add in or code to display the entire structure of a query that has multiple levels of dependency, e.g. Query 1 uses Query 2 which uses Queries 3, 4 and 5, each of which use further queries and/or tables ?
I want to see the paths from Query 1 through all the source queries all the way down to the source tables.

I can program this, but don't want to if someone has done it already.

Thanks

 

[MarkK]

Here's SQL for a query that shows all the queries in your Db, and a list of referenced objects for each query. It uses system tables MSysObjects and MSysQueries. You could extend this to whatever depth, or write code using these ideas to construct query trees fairly easily.

[COLOR="Green"]'copy and paste this sql into a new query[/COLOR]
SELECT o.Id, o.Name, o1.Name AS [References], o1.Type AS TypeOf
FROM MSysObjects AS o1 RIGHT JOIN (MSysQueries AS q INNER JOIN MSysObjects AS o ON q.ObjectId = o.Id) ON o1.Name = q.Name1
WHERE (((o.Type)=5) AND ((o.Flags)<>3) AND ((o1.Type) Is Not Null));

Hope this helps,

 

[Galaxiom]

Fantastic. Would there be an equivalent for SQL Server?

 

[rich.barry]

MarkK - that's a great bit of SQL to have !

I had previously iterated through the QueryDefs and written them to a table, but your SQL presents it in a much a nicer format that shouldn't be too hard to turn into a tree.
Thanks for the help.

 

[MarkK]

rich, you bet. Cheers,

Galaxiom, I know zero (0) about SQL Server, but aren't there are a TON of system tables?

 

[rich.barry]

Here is the code for creating the dependency tree.....
It works well for me.

If you don't want to use the 'Type', you could replace it with arrays in the Sub instead.

Public Type jobListType
    Position As String
    Level As Integer
    ObjectName As String
End Type

Sub QueryDependencies()

On Error GoTo errortrap

Dim QueryName As String

Dim RecordIn As New ADODB.Recordset
Dim RecordOut As New ADODB.Recordset
Dim qdfTemp As QueryDef
Dim td As TableDef

Dim counter(100) As Integer
Dim objects(1000) As String
Dim jobList(1000) As jobListType

Dim sqlstr As String
Dim CurrentLevel As Integer
Dim posStr As String
Dim repeat As Boolean
Dim jobCt As Integer
Dim waitingCt As Integer
Dim n As Integer

QueryName = InputBox("Enter Query Name to trace dependencies.", "Query Dependency Trace")

'set up a table for results
DoCmd.DeleteObject acTable, "tblQueryDependencies"

Set td = CurrentDb().CreateTableDef("tblQueryDependencies")

td.Fields.Append td.CreateField("Position", dbText, 255)
td.Fields.Append td.CreateField("Level", dbInteger)
td.Fields.Append td.CreateField("ObjectName", dbText, 255)

CurrentDb().TableDefs.Append td
    
'process the query
RecordOut.Open "SELECT * FROM tblQueryDependencies;", CurrentProject.Connection, adOpenKeyset, adLockPessimistic

With RecordIn

    Do
        repeat = False
        
        sqlstr = "SELECT o.Name, o1.Name AS [References], o1.Type AS TypeOf FROM MSysObjects AS o1 RIGHT JOIN (MSysQueries AS q INNER JOIN MSysObjects AS o ON q.ObjectId = o.Id) ON o1.Name = q.Name1 GROUP BY o.Name, o1.Name, o1.Type, o.Type, o.Flags HAVING (((o.Name)='" & QueryName & "') AND ((o1.Type) Is Not Null) AND ((o.Type)=5) AND ((o.Flags)<>3)) ORDER BY o1.Type;"
        .Open sqlstr, CurrentProject.Connection, adOpenKeyset, adLockPessimistic
       
        If .RecordCount > 0 Then
            
            'write the parent record
            If CurrentLevel = 0 Then
                RecordOut.AddNew
                RecordOut!Position = "10"
                RecordOut!Level = 0
                RecordOut!ObjectName = QueryName
                RecordOut.Update
                
                posStr = "10-"
            End If
            
            .MoveFirst
            
            counter(CurrentLevel) = 10
            
            'loop through the objects
            Do While Not .EOF
                
                'if it is a query, add to the jobList as we need to see what makes up each query
                If !TypeOf = 5 Then
                    jobList(jobCt).Position = posStr & counter(CurrentLevel)
                    jobList(jobCt).Level = CurrentLevel + 1
                    jobList(jobCt).ObjectName = !References
                    jobCt = jobCt + 1
                    waitingCt = waitingCt + 1
                End If
                
                'write it to the table
                RecordOut.AddNew
                RecordOut!Position = posStr & counter(CurrentLevel)
                RecordOut!Level = CurrentLevel + 1
                RecordOut!ObjectName = !References
                RecordOut.Update
                
                counter(CurrentLevel) = counter(CurrentLevel) + 1
                
                .MoveNext
                
            Loop
            
            .Close
        End If
        
        'find the next object to examine
        If waitingCt > 0 Then
            For n = 0 To jobCt - 1
                If jobList(n).ObjectName <> "" Then
                    QueryName = jobList(n).ObjectName
                    jobList(n).ObjectName = ""
                    CurrentLevel = jobList(n).Level
                    posStr = jobList(n).Position & "-"
                    waitingCt = waitingCt - 1
                    repeat = True
                    Exit For
                End If
            Next n
        End If
        
    Loop While repeat = True

End With

RecordOut.Close

Set RecordIn = Nothing
Set RecordOut = Nothing

'open the output view
DoCmd.DeleteObject acQuery, "qry_XTab_QueryDependencies"

Set qdfTemp = CurrentDb().CreateQueryDef("qry_XTab_QueryDependencies", "TRANSFORM Min(tblQueryDependencies.ObjectName) AS MinOfObjectName SELECT tblQueryDependencies.Position FROM tblQueryDependencies GROUP BY tblQueryDependencies.Position ORDER BY tblQueryDependencies.Position PIVOT tblQueryDependencies.Level;")

DoCmd.OpenQuery "qry_XTab_QueryDependencies"

Exit Sub


errortrap:

Select Case Err

Case 7874
    Resume Next
Case Else
    MsgBox ("Error " & Err & " : " & Error(Err))
End Select

End Sub