Parsing SQL

[PhilOfWalton]

I think this one is impossible, but I throw open the challenge

Here is a small section of SQL

SELECT DISTINCTROW Address.*, Towns.Town, Counties.County, Country.Country, IIf([Address1]>"",[Address1]) & IIf([Address2]>"",", " & [Address2]) & IIf([Address3]>"",", " & [Address3]) & IIf([Town]>"",", " & [Town]) & IIf([County]>"",IIf([Town]<>[County],", " & [County])) & IIf([PostCode]>"",", " & Format(Nz([PostCode],"!@@@@ @@@"),Nz([Country]))) & IIf([Country]>"" And [Country]<>[County],", " & [Country]) AS MemAdd
 

I am trying to find all the field names, and successfully find (Ignoring the Address.* which I can expand) Town, County, Country & MemAdd.

What I want to do is find the expression that is used for MemAdd (the convoluted IIf clause)

Before I start parsing the SQL I try to eliminate some of the likely problems using

    TempStrSQL = Replace(StrSQL, Chr$(10) & Chr$(13), " ")      ' Remove line feeds
    TempStrSQL = Replace(TempStrSQL, Chr$(13), " ")             ' Remove CR
    TempStrSQL = Replace(TempStrSQL, "!", ".")                  ' Possible expanded version
     ' Need to check for commas in strings
    TempStrSQL = Replace(TempStrSQL, " & " & Chr$(34) & ", " & Chr$(34) & " & ", " & " & Chr$(34) & "~ " & Chr$(34) & " & ")
    TempStrSQL = Replace(TempStrSQL, " & " & Chr$(34) & "," & Chr$(34) & " & ", " & " & Chr$(34) & "~" & Chr$(34) & " & ")
 

Where StrSQL is the "Input SQL"

The main fields I find by looking for a comma space combination,
but with all the commas in the IIf clause - no chance.

The end idea is to be able to take any SQL statement and get different sections of it like
The Field List
The FROM Tables section
The WHERE Section
The ORDER BY Section
and as the above question implies, if the Field is a calculated field, the calculation.

If anyone has any ideas, here is what I have got so far

Option Compare Database
Option Explicit
 
    Public Type SQLInfo
        SQLType As Integer          ' SELECT DISTINCTROW, SELECT DISTINT, SELECT, INSERT, DELETE, UNION
        UNIONStart As Long
        FROMStart As Long
        WHEREStart As Long
        ORDERBYStart As Long
        SEMICOLONStart As Long
        TheQuery As String
        FROMTables As String
        WHEREClause As String
        ORDERBYClause As String
        ASClauses() As String
    End Type
 
Public Function AnalyseSQL(StrSQL As String) As SQLInfo
'Debug.Print AnalyseSQL(Forms!GetInfo!OldQuery)
 
    Dim SQLInf As SQLInfo
    Dim TempStrSQL As String
    Dim Lngi As Long, Lngj As Long, Lngk As Long
    Dim FROMStart As Long
    Dim WHEREStart As Long
    Dim ORDERBYStart As Long
    Dim SEMICOLONStart As Long
    Dim TheQuery As String
    Dim FROMTables As String
    Dim WHEREClause As String
    Dim ORDERBYClause As String
    Dim ASClauses() As String
    Dim i As Integer
 
    TempStrSQL = Replace(StrSQL, Chr$(10) & Chr$(13), " ")      ' Remove line feeds
    TempStrSQL = Replace(TempStrSQL, Chr$(13), " ")             ' Remove CR
    TempStrSQL = Replace(TempStrSQL, "!", ".")                  ' Possible expanded version
     ' Need to check for commas in strings
    TempStrSQL = Replace(TempStrSQL, " & " & Chr$(34) & ", " & Chr$(34) & " & ", " & " & Chr$(34) & "~ " & Chr$(34) & " & ")
    TempStrSQL = Replace(TempStrSQL, " & " & Chr$(34) & "," & Chr$(34) & " & ", " & " & Chr$(34) & "~" & Chr$(34) & " & ")
 
 
    Lngi = InStr(TempStrSQL, "SELECT DISTINCTROW ")
    If Lngi = 1 Then
        SQLInf.SQLType = 1          ' SELECT DISTINCTROW
        GoTo CheckFROM
    End If
 
    Lngi = InStr(TempStrSQL, "SELECT DISTINCT ")
    If Lngi = 1 Then
        SQLInf.SQLType = 2          ' SELECT DISTINCT
        GoTo CheckFROM
    End If
 
    Lngi = InStr(TempStrSQL, "SELECT ")
    If Lngi = 1 Then
        SQLInf.SQLType = 3          ' SELECT
        GoTo CheckFROM
    End If
 
    Lngi = InStr(TempStrSQL, "INSERT ")
    If Lngi = 1 Then
        SQLInf.SQLType = 4          ' INSERT
        GoTo CheckFROM
    End If
 
    Lngi = InStr(TempStrSQL, "DELETE ")
    If Lngi = 1 Then
        SQLInf.SQLType = 5          ' DELETE
        GoTo CheckFROM
    End If
 
    Lngi = InStr(TempStrSQL, "UPDATE ")
    If Lngi = 1 Then
        SQLInf.SQLType = 6          ' UPDATE
        GoTo CheckFROM
    End If
 
    Lngi = InStr(TempStrSQL, "UNION SELECT ")
    If Lngi > 1 Then
        SQLInf.SQLType = 7          ' UNION
        SQLInf.UNIONStart = Lngi
        GoTo CheckFROM
    End If
 
CheckFROM:
    FROMStart = InStr(TempStrSQL, "FROM ")
    If FROMStart > 1 Then
        SQLInf.FROMStart = FROMStart
        TheQuery = Left(TempStrSQL, FROMStart - 2)
        SQLInf.TheQuery = Trim(TheQuery)
    End If
 
    ' Work out the "Where clause
CheckWHERE:
    WHEREStart = InStr(TempStrSQL, "WHERE ")
    If WHEREStart > 0 Then
        ORDERBYStart = InStr(WHEREStart, TempStrSQL, "ORDER BY ")
    Else
        ORDERBYStart = InStr(TempStrSQL, "ORDER BY")
    End If
    If ORDERBYStart > 0 Then
        SEMICOLONStart = InStr(ORDERBYStart, TempStrSQL, ";")
    Else
        SEMICOLONStart = InStr(TempStrSQL, ";")
    End If
 
    ' Work out the where criteria
    If WHEREStart > 0 Then
        If ORDERBYStart > 0 Then
            WHEREClause = Mid$(TempStrSQL, WHEREStart, ORDERBYStart - WHEREStart)
        Else
            If SEMICOLONStart > 0 Then
                WHEREClause = Mid$(TempStrSQL, WHEREStart, SEMICOLONStart - WHEREStart)
            Else
                WHEREClause = Mid$(TempStrSQL, WHEREStart, Len(TempStrSQL) - WHEREStart)
            End If
        End If
        SQLInf.WHEREClause = Trim(WHEREClause)
 
        If ORDERBYStart > 0 Then
            If SEMICOLONStart > 0 Then
                ORDERBYClause = Mid(TempStrSQL, ORDERBYStart, SEMICOLONStart - ORDERBYStart)
            Else
                ORDERBYClause = Mid(TempStrSQL, ORDERBYStart)
            End If
            SQLInf.ORDERBYClause = Trim(ORDERBYClause)
        End If
 
        If FROMStart > 0 Then
            If WHEREStart > 0 Then
                FROMTables = Mid(TempStrSQL, FROMStart, WHEREStart - FROMStart)
            Else
                If ORDERBYStart > 0 Then
                    FROMTables = Mid(TempStrSQL, FROMStart, WHEREStart - ORDERBYStart)
                Else
                    If SEMICOLONStart > 0 Then
                        FROMTables = Mid(TempStrSQL, FROMStart, WHEREStart - SEMICOLONStart)
                    End If
                End If
            End If
            SQLInf.FROMTables = Trim(FROMTables)
        End If
    Else                                    ' No WHERE
        If ORDERBYStart > 0 Then
            If SEMICOLONStart > 0 Then
                ORDERBYClause = Mid(TempStrSQL, ORDERBYStart, SEMICOLONStart - ORDERBYStart)
            Else
                ORDERBYClause = Mid(TempStrSQL, ORDERBYStart)
            End If
            SQLInf.ORDERBYClause = Trim(ORDERBYClause)
        End If
 
        If FROMStart > 0 Then
            If WHEREStart > 0 Then
                FROMTables = Mid(TempStrSQL, FROMStart, WHEREStart - FROMStart)
            Else
                If ORDERBYStart > 0 Then
                    FROMTables = Mid(TempStrSQL, FROMStart, ORDERBYStart - FROMStart)
                Else
                    If SEMICOLONStart > 0 Then
                        FROMTables = Mid(TempStrSQL, FROMStart, SEMICOLONStart - FROMStart)
                    End If
                End If
            End If
            SQLInf.FROMTables = Trim(FROMTables)
        End If
    End If
 
    ' Look for AS in the query (from SELECT to FROM)
NextAS:
    Lngi = InStr(Lngi + 1, TheQuery, " AS ")
    If Lngi > 0 Then                                    ' AS found
        Lngj = InStr(Lngi + 5, TheQuery, ", ")          ' Following Comma
        If Lngj = 0 Then                                ' Last field
            Lngj = InStr(Lngi + 5, TheQuery, " FROM ")
        End If
        Lngk = InStrRev(TheQuery, ", ", Lngi + 5)        ' Preceeding Comma
        If Lngk = 0 Then                                ' First field
            If SQLInf.SQLType = 1 Then                  ' SELECT DISTINCTROW
                Lngk = Len("SELECT DISTINCTROW ")
            ElseIf SQLInf.SQLType = 2 Then              ' SELECT DISTINCT
                Lngk = Len("SELECT DISTINCT ")
            ElseIf SQLInf.SQLType > 2 And SQLInf.SQLType < 6 Then ' SELECT, INSERT, FELET, UPDATE
                Lngk = 7
            End If
        End If
       ' Stop
        i = i + 1
        ReDim Preserve ASClauses(2, i)
        ASClauses(1, i) = Trim(Mid(TheQuery, Lngi + 4, Lngj - Lngi - 4))     ' The combined field name
        ASClauses(2, i) = Trim(Mid(TheQuery, 7, Lngi - Lngk))               ' Separate fields including " AS "
        GoTo NextAS
    End If
 
    AnalyseSQL = SQLInf
 
End Function
 

Sorry it's a touch long winded but for some queries it is working, but not all

Phil

[ADezii]

1. I may be way off base here, but how about creating a Recordset based on the SQL Statement and then return the Members (Field Names) of the Fields Collection. The following SQL Statement is from the Northwind Sample Database, and the actual Query is named Invoice Data. This Statement is valid. The following Code will return all Fields from the Recordset Object which is based on the SQL Statement (strSQL).
2. Code Definition:
   

   Dim strSQL As String
   Dim MyDB As DAO.Database
   Dim rst As DAO.Recordset
   Dim fld As DAO.Field
   Dim intCtr As Integer
    
   strSQL = "SELECT Orders.[Order ID], Orders.[Ship Name], Orders.[Ship Address], Orders.[Ship City], Orders.[Ship State/Province], " & _
            "Orders.[Ship ZIP/Postal Code], Orders.[Ship Country/Region], Orders.[Customer ID], Customers.Company AS [Customer Name], " & _
            "Customers.Address, Customers.City, Customers.[State/Province], Customers.[ZIP/Postal Code], Customers.[Country/Region], " & _
            "[Employees Extended].[Employee Name] AS Salesperson, Orders.[Order Date], Orders.[Shipped Date], Shippers.Company AS " & _
            "[Shipper Name], [Order Details].[Product ID], Products.ID AS [Product ID], [Order Details].[Unit Price], " & _
            "[Order Details].Quantity, [Order Details].Discount, CCur(Nz([Unit Price]*[Quantity]*(1-[Discount]),0)/100)*100 AS " & _
            "ExtendedPrice, Orders.[Shipping Fee], Products.[Product Name] " & _
            "FROM (Shippers RIGHT JOIN (Customers RIGHT JOIN (Orders LEFT JOIN [Employees Extended] ON Orders.[Employee ID] = " & _
            "[Employees Extended].ID) ON Customers.ID = Orders.[Customer ID]) ON Shippers.ID = Orders.[Shipper ID]) LEFT JOIN " & _
            "([Order Details] LEFT JOIN Products ON [Order Details].[Product ID] = Products.ID) ON Orders.[Order ID] = " & _
            "[Order Details].[Order ID];"
    
   Set MyDB = CurrentDb
   Set rst = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
    
   Debug.Print "Counter", "Fields Used in Query"
   Debug.Print String(50, "-")
    
   For Each fld In rst.Fields
     intCtr = intCtr + 1
       Debug.Print Format$(intCtr, "000"), fld.Name
   Next
    
   Debug.Print String(50, "-")
    
   rst.Close
   Set rst = Nothing
    

3. OUTPUT:
   

   Counter       Fields Used in Query
   --------------------------------------------------
   001           Order ID
   002           Ship Name
   003           Ship Address
   004           Ship City
   005           Ship State/Province
   006           Ship ZIP/Postal Code
   007           Ship Country/Region
   008           Customer ID
   009           Customer Name
   010           Address
   011           City
   012           State/Province
   013           ZIP/Postal Code
   014           Country/Region
   015           Salesperson
   016           Order Date
   017           Shipped Date
   018           Shipper Name
   019           Order Details.Product ID
   020           Product ID
   021           Unit Price
   022           Quantity
   023           Discount
   024           ExtendedPrice
   025           Shipping Fee
   026           Product Name
   --------------------------------------------------
    

[ADezii]

I failed to add that this would only be a part of the puzzle, namely the Field List.

[PhilOfWalton]

Thanks for your input. I already load the fields into a table, but I think I might gave a go at using instr to find the field name in the SQL, see if it is immediately preceded by " AS " and if so use InstrRev to find the previous field name. If that succeeds, assume everything in between is the expression (calculation or concatination of fields etc.) that is source of the AS field.

So in the above North Wind example, find [Customer Name] in the table, scan for it in the SQL, establish there is an " AS " in front if it, find the preceding field ([Customer ID]) in the table, scan backward for that in the SQL and assume that everything between (Customers.Company) is the source of Customer Name.

Does that look logical?

But that's for tomorrow.

Thanks again

Phil

[ADezii]

1. Initially, I took a rather convoluted approach that processes SQL Statements, in this case the one displayed below, and produces an Output Table with the results.
2. SQL:
   

   strSQL = "SELECT Employees.[Last Name], Employees.[First Name], Employees.[E-mail Address], Employees.Company, " & _
            "Employees.[Job Title] FROM Employees WHERE (((Employees.[Job Title]) Like 'Sales*')) " & _
            "ORDER BY Employees.[Last Name], Employees.[First Name];"
    

3. Results Table:
   

   ID    ObjType         ObjName              Context
   291    Field          Last Name            Display
   292    Table          Employees            FROM Clause
   293    Table/Field    Employees.Last Name  PRIMARY ORDER BY Clause
   294    Field          First Name           Display
   295    Table          Employees            FROM Clause
   296    Table/Field    Employees.First Name SECONDARY ORDER BY Clause
   297    Field          E-mail Address       Display
   298    Table          Employees            FROM Clause
   299    Field          Company              Display
   300    Table          Employees            FROM Clause
   301    Field          Job Title            Display
   302    Table          Employees            FROM Clause
   303    Table/Field    Employees.Job Title  WHERE Clause

4. Sorry, did not have the time to properly format the Results.

[NeoPa]

Hi Phil.

If I may offer some advice, without necessarily leading the way, I'd suggest you take a different approach and parse the SQL itself using your own routine. We all have a basic understanding of what keywords are available and what punctuation does what and where. That would be more complicated in a way, but would work from the ground-up, as it were. Rather than trying to recognise items generally, which can easily be tripped up by unexpected SQL.

Clearly, your code would have to understand various modes and would behave differently when it came across a parenthesis or bracket when inside a string than it would outside of one. String literals are the killer here it seems to me. When you start using VBA code, and even SQL code, to build up SQL strings then it's not uncommon to have valid SQL structures within your string literals.

Don't get me wrong. I'm not invested in your taking the approach I suggest. However, I thought I'd throw it out there for your consideration.

Best of luck however you choose to proceed from here.

[jforbes]

This looked like fun, so I created a function that might work for you. The idea is to pass it a list of SQL fields and it will return a Pipe Delimited string of just the fields that contain AS, while taking into account the nesting that happens with Parenthesis.

Here is the code:

Public Function getDelimitedASFields(ByRef sSQL As String) As String
 
    Dim iCount As Integer
    Dim iStart As Integer
    Dim INestCount As Integer
    Dim iFound As Integer
    Dim iAsCount As Integer
    Dim iSQLLen As Integer
    Dim iLastFound As Integer
    Dim sResult As String
 
    iSQLLen = Len(sSQL)
 
    ' Loop through the Fields and grab the fields with he AS Keyword and place them in a Pipe delimited String
    For iCount = 1 To iSQLLen
        Select Case Mid(sSQL, iCount, 1)
 
            ' Commas signify a new field unless nested, like in a function
            Case ","
                If INestCount = 0 Then
                    If iAsCount = 4 Then
                        iFound = iFound + 1
                    Else
                        iStart = iCount
                    End If
                End If
 
 
            ' flag when in a nested function so that commas are ignored
            Case "("
                INestCount = INestCount + 1
                If iAsCount <> 4 Then iAsCount = 0
            Case ")"
                INestCount = INestCount - 1
                If iAsCount <> 4 Then iAsCount = 0
 
 
            ' look for the "AS" Keyword
            Case " "
                If iAsCount = 0 Or iAsCount = 3 Then iAsCount = iAsCount + 1
            Case "A"
                If iAsCount = 1 Then iAsCount = iAsCount + 1
            Case "S"
                If iAsCount = 2 Then iAsCount = iAsCount + 1
 
 
            ' Reset the "AS" Keyword counter as needed
            Case Else
                If iAsCount <> 4 Then iAsCount = 0
        End Select
 
 
 
        ' See if an "AS" was found, and we found a non-nested comma, replace the comma with a pipe
        If iAsCount = 4 And (iLastFound <> iFound Or iCount = iSQLLen) Then
            sResult = sResult & Trim(Mid(sSQL, (iStart + 1), (iCount - iStart) + (iLastFound <> iFound))) & "|"
            Debug.Print sResult
            iAsCount = 0
            iStart = iCount
            iLastFound = iFound
        End If
    Next iCount
 
    If Len(sResult) > 0 Then sResult = Left(sResult, Len(sResult) - 1)
 
 
    ' Return the results
    getDelimitedASFields = sResult
 
End Function

Using this as a parameter:"iif(true, 1, 2) AS v, a as b, Employees.[Last Name], Employees.[First Name], Employees.[E-mail Address], Employees.Company,Employees.[Job Title] , b as c, c, d as e, Address.*, Towns.Town, Counties.County, Country.Country, Iif([Address1]>"",[Address1]) & Iif([Address2]>"",", " & [Address2]) & Iif([Address3]>"",", " & [Address3]) & Iif([Town]>"",", " & [Town]) & Iif([County]>"",Iif([Town]<>[County],", " & [County])) & Iif([PostCode]>"",", " & Format(Nz([PostCode],"!@@@@ @@@"),Nz([Country]))) & Iif([Country]>"" And [Country]<>[County],", " & [Country]) AS MemAdd, Employees.[Last Name], Employees.[First Name], Employees.[E-mail Address], Employees.Company,Employees.[Job Title]"

The function returns: "iif(true, 1, 2) AS v|a as b|b as c|d as e|Iif([Address1]>"",[Address1]) & Iif([Address2]>"",", " & [Address2]) & Iif([Address3]>"",", " & [Address3]) & Iif([Town]>"",", " & [Town]) & Iif([County]>"",Iif([Town]<>[County],", " & [County])) & Iif([PostCode]>"",", " & Format(Nz([PostCode],"!@@@@ @@@"),Nz([Country]))) & Iif([Country]>"" And [Country]<>[County],", " & [Country]) AS MemAdd"

You can then split on the Pipe.

[ADezii]

1. If I am not mistaken, there is a much easier way to reference the Expression that generates an Aliased Field via one of the System Tables, namely MSysQueries.
2. Case in point is the Employees Extended Query in the Northwind Sample Database. Here is it's SQL:
   

   SELECT IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[Last Name] & ", " & [First Name])) AS [File As], IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[First Name] & " " & [Last Name])) AS [Employee Name], Employees.*
   FROM Employees
   ORDER BY IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[Last Name] & ", " & [First Name])), IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[First Name] & " " & [Last Name]));

3. Assuming we want to return the Expression that generated the Aliased [Employee Name] Field:
   

   Dim strASFld As String
    
   strASFld = "Employee Name"
    
   Debug.Print DLookup("[Expression]", "MSySqueries", "[Name1] = '" & strASFld & "'")

4. The OUTPUT (Expression that generated the AS Field) would be:
   

   IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[First Name] & " " & [Last Name]))

5. Make use of the System Table.

[PhilOfWalton]

Thanks both for replying. Using MsysQueries looks the way to go.

I will do further playing in the next few days and report back.

Interestingly, and probably logically you can't create a query on MsysQueries. Suppose it would be a circular reference.

Thanks again,

Phil

[ADezii]

Not to put a Monkey Wrench into the process, but you can have Multiple Aliased Field Names all named exactly the same, such as: [Employee Name]. What would be different would be the [ObjectId] Field in MSysQueries. You can check this [ObjectId] against the [Id] Field in MSysObjects. The [Name] Field in MSysObjects for the [Id] is the Name of the Query.

[ADezii]

Interestingly, and probably logically you can't create a query on MsysQueries.

I'm pretty sure that you can.

1. The following Query
   

   SELECT MSysQueries.Attribute, MSysObjects.Name AS [Query Name], MSysQueries.Name1 AS [AS Field], MSysQueries.Expression, MSysQueries.ObjectId
   FROM MSysObjects INNER JOIN MSysQueries ON MSysObjects.Id = MSysQueries.ObjectId
   WHERE (((MSysQueries.Name1)="Employee Name"));

2. will produce the following OUTPUT
   

   Attribute     Query Name            AS Field         Expression                                          ObjectId (after Expression)
   6             Employees Extended    Employee Name    "IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[First Name] & "" "" & [Last Name]))"    -2147483636
    

[PhilOfWalton]

How odd

I did it before, even more simply

SELECT MSysQueries.*
FROM MSysQueries;
 

and got a blank sheet. Now running perfectly.

Anyway, a lot to redesign using MSysQueries so I will get back in a few days.

Thanks

Phil

[ADezii]

Good Luck, Phil. I'm sure that you are aware but you can Link MSysQueries with MSysObjects (as illustrated in Post# 11) in the following manner:

MSysObjects.Id = MSysQueries.ObjectId

with [ObjectId], [Attribute], and [Order] in MSysQueries comprise a Multi-Field Primary Key.

[ADezii]

1. I did a little research on the MSysQueries System Table and came up with what I feel is invaluable information, especially given your Post# 9. I consolidated as much info as I could and placed it in an Access Table. To verify much of this information, I created the following Query linking the MSysObjects and MSysQueries Tables and filtering for Queries only (Type=5) and Non-Temporary and Queries not involved in Row or Record Sources (Not Like ~sq_*). I found it extremely interesting and I hope you will also especially since you will use this Table in your Current Project. If you want the actual Table, I will be happy to Attach a DB containing it.
   

   SELECT MSysQueries.ObjectId, MSysObjects.Type, MSysObjects.Name, MSysQueries.Attribute, MSysQueries.Expression, MSysQueries.Flag, MSysQueries.Name1, MSysQueries.Name2
   FROM MSysObjects INNER JOIN MSysQueries ON MSysObjects.Id = MSysQueries.ObjectId
   WHERE (((MSysObjects.Type)=5) AND ((MSysObjects.Name) Not Like "~sq*"));

2. tblMSysObjects:
   

   --------------- qryMSysObjects---------------
    
   Attribute    Flag Value    COMMENT2
   0        Indicates the Beginning of the Query Definition
   255        Indicates the End of the Query Definition
   1        Attribute 1 indicates the Type of Query
   1    1    SELECT Query
   1    2    SELECT INTO or MAKE TABLE Query
   1    3    INSERT QUERY; [Name1] will have the Name of the Table to insert into
   1    4    UPDATE Query
   1    5    DELETE Query
   1    6    CROSSTAB Query (TRANSFORM)
   1    7    CREATE TABLE Query
   1    8    DATE/TIME Parameter
   1    9    UNION Query; Text of SQL in [Expression]
   2        Rows with Attribute 2 (can be multiple) are each of the Formal Parameters of the Query as
           defined in the Query Parameters Dialog Box. The Flag Column indicates the Data Type
           (i.e. '10' for dbText) and the [Name1] Column indicates the Name of the Parameter. If there
           are no Rows with Attribute 2, then the Query does not have 'Formal' Parameters.
   3        Attribute 3 indicates the presence of UNION or DISTINCT Keywords.
   3    0    Nothing special.
   3    1    UNION ALL
   3    2    SELECT DISTINCT
   3    3    UNION
   3    8    SELECT DINTINCTROW
   3    9    Queries on Master Fields and Child Fields
   3    12    WITH OWNER AACCESS OPTION
   3    16    SELECT TOP; the number as in TOP 10 will be in the [Name1] Field
   3    24    SELECT TOP
   3    48    SELECT TOP [Name1] PERCENT; PERCENT IN [Name1]
   4        Attribute 4 indicates if the Query comes from an External Database. [Name1] will contain
           the Source if Attribute 4 exists.
   5        Attribute 5 (could be multiple) indicates each Table found in a Query. If the Query is a
           UNION Query, the [Expression] Field has a split on the UNION Keyword and the [Name2]
           Field has a System-generated Table Alias. For all other Tables in a Query, [Name1] is the
           Name of the Table and [Name2] is the Alias, if there is one.
   6        Rows with Attribute 6 (could be multiple) indicated each single Field or Expression in the Query. If there is no
           Attribute 6 for the Query, the behavior assumed is that all Fields are included. The [Expression]
           Field contains each Field Expression or name, and [Name1] contains the Field Alias, if there is
           one.
   6    0    Value of the Field or Expression.
   6    1    The Field is a Column Heading in a CROSSTAB Query.
   6    2    The Field is a Row Heading in a CROSSTAB Query.
   7        Rows with Attribute 7 (could be multiple) indicate each single Join 'ON' Expression. The
           [Expression] Field contains the actual Join Expression.
   7    1    [Name1] INNER JOIN [Name2] on [Expression]
   7    2    [Name1] LEFT JOIN [Name2] on [Expression]
   7    3    [Name1] RIGHT JOIN [Name2] on [Expression]
   8        The Row with Attribute 8 contains the whole WHERE Claus in the [Expression] Field. If there
           is no WHERE Clause, Attribute 8 is omitted from the Query.
   9        The Rows with Attribute 9 (could be multiple) indicate each single GROUP BY Expression in the
           GROUP BY Clause of the Query. The [Expression] Field contains each GROUP BY Expression.
   9    0    Value of the Field or Expression.
   9    1    The Field is a Column Heading in a CROSSTAB Query.
   9    2    The Field is a Row Heading in a CROSSTAB Query.
   10        HAVING [Expression]
   11        The Rows with Attribute 11 (could be multiple) indicate each single ORDER BY Expression in
           the ORDER BY Clause of the Query. The [Expression] Field contains each ORDER BY
           Expression. [Name1] has a 'D' or 'd' to indicated that the Sort is done in Descending Order.
    
    

3. The [Attribute] and [Flag] Fields in MSysQueries taken together clearly appear to define virtually all of a Query's Properties.

P.S. - Please forgive the sloppy formatting, ran out of time and had to go!