Here is a Query I used in the past.
This was initial query I wrote which makes use of lot of temp tables.
Thanks for the reply and some ideas. I have tried many different approaches to this and here is what I have so far.
I am struggling on how I am going to get this done for all child assemblies under an assembly because there could be 1 or more child assemblies under the parent, so I can't just repeat same query for a set number of times.
This is a great starting point Ravi, thank you. As Earl is pointing out, a problem with it is that it stops one level down in the reference tree.
How much more work is it, to make it able to run through all levels in a large assembly structure?
Does anyone know, or does anyone have this already?
Sadly, my query language knowledge falls short here.
This query is more complicated than most think due to what Earl has pointed out with levels. One thing that might help you is a query provided by SOLIDWORKS on their Knowledge Base (if you dig deep enough) on how they generate the Contains tab. That solution number is S-059636 and the query to that solution should be attached to this thread.
What's useful with that query is that it uses a while loop to increment the level number, and adds the references to a table variable. From this query you could potentially relation the other data you need.
Contains.sql.zip 1.3 KB
Also a great excuse to play around with recursive common table expressions:
with xrefs_rec as (select DocumentID as TLA_DocumentID, DocumentID, XrefDocument, RevNr as TLA_RevNr, RevNr, XrefRevNr, XrefID, 0 as Level from XRefs where XrType = 1 union all select xrr.TLA_DocumentID, xr.DocumentID, xr.XrefDocument, TLA_RevNr, xr.RevNr, xr.XrefRevNr, xr.XrefID, xrr.Level + 1 as Level from XRefs xr inner join xrefs_rec xrr on xrr.XRefDocument = xr.DocumentID and xrr.XRefRevNr = xr.RevNr where XrType = 1) select d1.filename as TLA, d2.filename as Parent, d3.filename as Child, Level from xrefs_rec xrr inner join documents d1 on d1.DocumentID = xrr.TLA_DocumentID and d1.LatestRevisionNo = xrr.TLA_RevNr inner join documents d2 on d2.DocumentID = xrr.DocumentID inner join documents d3 on d3.DocumentID = xrr.XrefDocument where d1.documentid = 20001