What does the data in MSysQueries mean?

Question

I've been using VBA to examine all the queries, forms, and modules in my Access 2000 database, but it can be quite tedious and slow. Recently, I decided to take a closer look at the system tables in Access, in particular, MSysQueries and MSysObjects. Can I use these tables to examine my objects in the database faster? Of course, these tables are read-only, so I can't make any modifications to the database through them without returning to VBA. What do the attributes in MSysQueries mean?

Recommend

Answer 1

Well, I came across this post on Google groups. I did further investigation on my own tables and wanted to share a table of information that I created inspired by work already done.

Each query can take up multiple rows in the table.

The row with attribute 0 is the beginning of the query.

The row with attribute 1 indicates the type of the query.

• Flag value 1 = SELECT query.
• Flag value 2 = SELECT ... INTO query, or a make table query. Name1 will have the name of the table that is created.
• Flag value 3 = INSERT query; Name1 will have the name of the table to insert to.
• Flag value 4 = UPDATE query
• Flag value 5 = DELETE query
• Flag value 6 = Crosstab query (TRANSFORM)
• Flag value 9 = UNION query

The rows with attribute 2 (there could be multiple) are each formal parameter of the query. 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.

The row with attribute 3 indicates the presence of UNION or DISTINCT keywords.

• Flag value 0 = Nothing special
• Flag value 1 = UNION ALL
• Flag value 2 = SELECT DISTINCT
• Flag value 3 = UNION
• Flag value 8 = SELECT DISTINCTROW
• Flag value 9 = Queries on master fields and child fields

The row with attribute 4 indicates if the query comes from an external database. Name1 will contain the source if attribute 4 exists.

The rows with attribute 5 (there could be multiple) indicate each table found in the 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.

The rows with attribute 6 (there could be multiple) indicate 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.

• Flag value 0 = Value of the field or expression
• Flag value 1 = The field is a column heading in a crosstab query.
• Flag value 2 = The field is a row heading in a crosstab query.

The rows with attribute 7 (there could be multiple) indicate each single join "ON" expression. The Expression field contains the actual join expression. Name1 contains the first table in the join. Name2 contains the second table in the join.

• Flag value 1 = Inner Join
• Flag value 2 = Left Join
• Flag value 3 = Right Join

The row with attribute 8 contains the whole WHERE clause in the Expression field. If there is no where clause, attribute 8 is omitted from the query.

The rows with attribute 9 (there 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.

• Flag value 0 = Value of the field or expression
• Flag value 1 = The field is a column heading in a crosstab query.
• Flag value 2 = The field is a row heading in a crosstab query.

The rows with attribute 11 (there 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 "D" or "d" to indicate that the sort is done in descending order.

The row with attribute 255 is the end of the query.

I'm not exactly sure what the Order field does, but I did find that it is not Null, and though it sometimes has a value of an empty string, it doesn't always have that value. Empty strings occur on attributes 5, 6, 7, and 9, but it is not always an empty string for those attributes.

Recommend

Attribute = 1 with Flag = 8 indicate a passthru query, Name1 returns Connect String – Darvon

I found cases of a working query having no row with attribute = 1 – Darvon

Answer 2

Thanks to @Bobort great explanations, I was able to create a query that lists all queries in current database, with their input tables/queries, query type, and target table (for action queries).
I thought I could share that here.

SELECT MSysObjects.Name AS queryName,  
Mid("SelectMakTblAppendUpdateDeleteXtab    777777PassThUnion ",([msysqueries]![Flag]-1)*6+1,6) AS queryType,  
src.Name1 AS [Input],  
MSysQueries.Name1 AS Target 
FROM (MSysQueries INNER JOIN MSysObjects ON MSysQueries.ObjectId =  MSysObjects.Id) 
LEFT JOIN (select * from MSysQueries WHERE Attribute = 5 )  AS src 
     ON  MSysQueries.ObjectId = src.ObjectId 
WHERE (((MSysObjects.Name)>"~z") AND ((MSysQueries.Attribute)  =1)) 
ORDER BY MSysObjects.Name, src.Name1;  

To use, just create a query in SQL view and paste the above code.

Darvon

Answer 3

Further to Bobort and iDevlop's answers:

The row with attribute 1 indicates the type of the query.

• Flag value 7 = DDL Query (eg CREATE TABLE...)
• Flag value 9 = Pass through Query

The row with attribute 3 indicates the predicate.

• Flag value 1 = All values, or UNION ALL (if a UNION query)
• Flag value 4 = WITH OWNERACCESS OPTION
• Flag value 16 = TOP N
• Flag value 48 = TOP N PERCENT

The rows with attribute 5 (there could be multiple) indicate each FROM table/query found in the query

• Expression contains the FROM source, or the SELECT statement if a UNION query

The row with attribute 10 contains the whole HAVING clause in the Expression field. If there is no HAVING clause, attribute 10 is omitted from the query.

The Order field is a BIG-ENDIAN binary value that contains an array of 4 bytes (binary fields can be added with VBA, but cannot be added using the UI, unless you copy and paste from a binary field in a system table.) However, in most databases, in the MSysQueries table, you're unlikely to encounter binary values greater than 255, so you can shortcut the conversion to a byte by inspecting the byte at index 3. For example:

Sub EnumOrder()

  Dim rst As Recordset
  Set rst = CurrentDb.OpenRecordset( _
    "  SELECT * FROM MSysQueries " & _
    "   WHERE Attribute = 6 " & _
    "ORDER BY ObjectId Asc, [Order] Asc")

  With rst
    Do While Not .EOF
      Debug.Print .Fields("ObjectId"), .Fields("Order")(3)
      .MoveNext
    Loop
    .Close
  End With

End Sub

Emblazonment

Answer 4

Cumulative values occur for Attribute 3. So additional items include:

• Flag 12 SELECT DISTINCT.... WITH OWNERACCESS OPTION
• Flag 18 SELECT DISTINCT TOP (i.e. 2+16)
• Flag 24 SELECT DISTINCTROW TOP (i.e. 8+16)
• Flag 50 SELECT DISTINCT TOP PERCENT (i.e. 2+48)
• Flag 56 SELECT DISTINCTROW TOP PERCENT (i.e. 8+48)

I have written an extended article about the workings of the MSysQueries table. See How Access stores queries.

Drive

Link above no longer works. – Delighted

Thanks for the prompt. I've updated the link and it now works again – Drive