part of SQL to show query sources Ms Access Gurus
Home Code

SQL > Document > Query underlying tables and queries

Have you ever wanted to get a list of each query's underlying tables and queries?

Below is SQL you can make a query with to give you that information.

Steps

  1. copy the SQL statement you want
    Don't copy more than just the SQL statement. Extra spaces and line breaks don't matter where there can be space ... but there may not be space before or after the SQL.
  2. create a new query
  3. switch to SQL view
  4. paste the SQL to document query tables
  5. Save as: qDocumentQueryTables_s4p
    or qDocumentQueryTables_withAppend_s4p if you are using the version that also shows the tablename being appended to for append queries.
  6. Switch to Datasheet view to look at the results

Example

These are partial results of the SQL statement run on a database with contact information.

The first column has the query name that is being documented. The second column is a underlying table or query name. The third column is the alias for the underlying table or query name, if it is different. The fourth column lists the Attribute: 5 means an underlying table or query; 1 means that NameTableQuery column what to append to for an append query.

If the underlying source is SQL for a subquery, NameTableQuery will be null.

example qDocumentQueryTables datasheet view

Logic

The data for this query comes from Microsoft system tables (MSysObjects, MSysQueries) with information Access stores about the object in your database.

Don't copy anything extra! -- just the SQL statement itself. SQL statements may not have space before or after them.

SQL


'*************** SQL Start *****************************************************
' Purpose  : List Underlying Tables and Queries for each query in a database
' Author   : crystal (strive4peace)
' License  : below SQL
' Code List: www.MsAccessGurus.com/code.htm
'--------------------------------------------------------------------------------


   qDocumentQueryTables_s4p


SELECT mO.Name AS QryName
 	, mQ.Name1 AS NameTableQuery
 	, mQ.Name2 AS AliasTableQuery
 FROM MSysObjects AS mO 
 	LEFT JOIN MSysQueries AS mQ 
 		ON mO.Id = mQ.ObjectId 
 WHERE ( ( mQ.Attribute=5) 
 	AND (Left(mo.Name,1) Not In ("~","{") ) ) 
 ORDER BY mO.Name, mQ.Name1;



   qDocumentQueryTables_withAppend_s4p


SELECT mO.Name AS QryName
 	, IIf(mQ.Attribute=1,"Append: ","") & mQ.Name1 AS NameTableQuery
 	, mQ.Name2 AS AliasTableQuery
 	, mQ.Attribute  
 FROM MSysObjects AS mO 
 	LEFT JOIN MSysQueries AS mQ 
 		ON mO.Id = mQ.ObjectId 
 WHERE ( Left( mo.Name,1) Not In ("~","{") )
        AND (   
        ( mQ.Attribute = 5)    
 	OR (
        ( mQ.Attribute = 1) 
 	AND ( mQ.Name1 Is Not Null) 
 	) )
 ORDER BY mO.Name
 	, mQ.Name1;
   
   
'--------------------------------------------------------------------------------'
'
' LICENSE
'   You may freely use and share this SQL for your projects,
'   just don't sell it alone or in a collection without my explicit written permission, thanks.
'   ~ crystal (strive4peace)  www.MsAccessGurus.com
'*************** SQL End *******************************************************

Goto Top  

Back Story

I've posted lots to document databases. Recently, someone asked about getting a list of underlying tables and queries for each query in a database. Here you go, Steve.

Share with others

here's the link to copy:

http://msaccessgurus.com/VBA/Code/sql_DocumentQueryTables.htm

Do you have something to say or share?

It is interesting to hear from you. Was something not clear? Did you find a bug? Is an explanation wrong or not sufficient? Do you want the code do more? (there is always more)

Some of you write to say thanks and share what you're doing with Access ... nice to hear from you! It is my hope that you build great applications with Access, design your database structure well, link to and share with data in other formats, and use other Office applications such as Excel, Word, and PowerPoint, ... take advantage of built-in abilities, use strengths of each product, and manage your information wisely.

Are you a developer? Do you want to share? Email to ask about getting your pages added to the code index.

When we communicate, collaborate, and appreciate, we all get better. Thank you. Email me at info@msAccessGurus.com ~crystal

Goto Top