1 Reply by charlesroper

  • charlesroper
  • charlesroper
  • Digital Development Manager
  • Offline
  • From: Field Studies Council
  • Registered: 26-03-2006
  • Posts: 906

Re: How does one find all tables related to a given table?

I'm sure no-one other than John Van Breda can answer this, but I'm posting this question here in the hope he has time to answer and so that all may benefit from the answer.

When an export is performed on, say, a Survey, Recorder is able to detect all of the data related to that survey and thus export it. My question is: how does Recorder perform this 'spidering' of the database in order to know what to export? Is it something that can be done in Transact-SQL? If so, how (if it's not too complex to explain)? If it isn't done purely in T-SQL, what is (roughly) going on?

Cheers,

Charles

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

charlesroper's Website

2 Reply by davec

  • davec
  • Veteran
  • Offline
  • From: Brecon, Powys, Wales
  • Registered: 11-05-2006
  • Posts: 108

Re: How does one find all tables related to a given table?

I'd like a definitive answer to this too, Charles.

Do you have a copy of the NBN Data Model? The physical implementation of that model closely mirrors the logical one. If you start tracing PKEY and FKEY table joins  from the Survey downwards to get a feel for the queries that are needed.  I use external scripting to do this to try and simplify the process. For example, I get a list of recorder's name keys and concatenate them to form one field.

A published document of the SQL used in Recorder would be a help to make sure we are all doing the right thing, and reuse it in our own software.

Dave Cope,
Biodiversity Technology Officer,
Biodiversity Information Service for Powys and Brecon Beacons National Park.

davec's Website

3 Reply by johnvanbreda

  • johnvanbreda
  • Administrator
  • Offline
  • From: UK
  • Registered: 27-04-2006
  • Posts: 1,237

Re: How does one find all tables related to a given table?

A quick answer for you - take a look in the Database_Relationship table. There are other ways of doing this in Transact SQL but this method allows us to control which tables are spidered.

John van Breda
Biodiverse IT

johnvanbreda's Website

4 Reply by charlesroper

  • charlesroper
  • charlesroper
  • Digital Development Manager
  • Offline
  • From: Field Studies Council
  • Registered: 26-03-2006
  • Posts: 906

Re: How does one find all tables related to a given table?

Thanks John, that looks like the ticket. Do you have a quick example of a query that uses this table? I'm having trouble wrapping my head around exactly how to use it.

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

charlesroper's Website

5 Reply by johnvanbreda

  • johnvanbreda
  • Administrator
  • Offline
  • From: UK
  • Registered: 27-04-2006
  • Posts: 1,237

Re: How does one find all tables related to a given table?

No problem. A example would be to retrieve all the tables that have a foreign key pointing to the sample table:

SELECT Detail_Table, Detail_Field 
FROM Database_Relationship 
WHERE Master_Table='sample'
John van Breda
Biodiverse IT

johnvanbreda's Website

6 Reply by TonyP

  • TonyP
  • TonyP
  • Veteran
  • Offline
  • From: Somerset
  • Registered: 15-05-2006
  • Posts: 306

Re: How does one find all tables related to a given table?

This looks good. Thanks for asking the question Charles and to John for the answer. Most helpful.

Data Manger
Somerset Environmental Records Centre

TonyP's Website