How to query what inputs (tables or queries) are used in a query?

[sqlaccess]

Hi,



I have an access database with a lot of tables and queries.

I have the sql to get all the table and query names as below:

However, what I want to do is to find the input tables and/or queries used in all of the queries in the database.

Any ideas guys?

Cheers

--this gets the list of table names in an access database
SELECT MSysObjects.Name AS table_name
FROM MSysObjects
WHERE (((Left([Name],1))<>"~")
AND ((Left([Name],4))<>"MSys")
AND ((MSysObjects.Type) In (1,4,6)))
order by MSysObjects.Name
;
--This gets the list of query names in an access database
SELECT MSysObjects.Name
FROM MSysObjects
WHERE type = 5
;

[John_G]

Hi -

This is valid for A2003; it should be valid for newer versions as well

There is another system table called msysQueries which contains all the other information you need. I think Attribute = 5 identifies the records you want. You would join the two tables using msysObjects.ID = msysQueries.ObjectID.

HTH

John

[sqlaccess]

Hi -

This is valid for A2003; it should be valid for newer versions as well

There is another system table called msysQueries which contains all the other information you need. I think Attribute = 5 identifies the records you want. You would join the two tables using msysObjects.ID = msysQueries.ObjectID.

HTH

John

Thanks John. Worked a treat!