>
> >> If you have a field name and want to find which table it's in quickly
> and
> >> efficiently, is there anything better than the following:
> >>
> >> SELECT table1
> >> =AFIELDS(aArray1)
> >> FOR i = 1 TO ALEN(aArray1,1)
> >> IF myfield = aArray1(i,1)
> >> itsintable1 = .T.
> >> ENDIF
> >> IF itsintable1 = .T.
> >> SELECT table2
> >> =AFIELDS(aArray2)
> >> FOR i = 1 TO ALEN(aArray2,1)
> >> IF myfield = aArray2(i,1)
> >> itsintable2 = .T.
> >> ENDIF
> >>
> >> ...or is this what I have to do, unless I work around it?
> >
> >If the tables are in the .DBC, then you could open the .DBC and
> >
> >select objectname ;
> > from {DBC} ;
> > where objectid in ;
> > (select parentid from {DBC} where ObjectType = "Field" and
> >ObjectName = m.MyField) ;
> > into array laTables
> >
> >/Paul
>
> I tried this suggestion, substituting my own names, without entirely
> understanding it. I suppose it is opening the .DBC as if it were a .DBF
> and returning my field name when it finds it in my table name. The array I
> got had repeated examples of the table name, plus the name of an old view
> which I thought I deleted. I wasn't sure what to do with it. Maybe it's
> time to clean up my .DBC. Thanks.
1: Add the 'DISTINCT' clause just after the first 'SELECT'
2: SET DELETED ON before you run this
3: include a second condition in the WHERE clause: WHERE ObjectType =
"Table" ...
Essentially the second SELECT grabs a list of all of the fields in the
table that match the name that you are looking for. It doesn't return
the name, rather it returns a list of the 'ParentId' fields. This
ParentId list is then checked and all of the corresponding ObjectName
fields are returned.
Things are done this way because the .DBC is a self referential table.
The ObjectId is unique, and it points to all types of objects (Tables,
Fields, etc.). The ParentId field indicates to which other object the
the current object belongs to. Thus if a certain Table has an ObjectId
of 3, all of the fields in that table will have a ParentId of 3.
HTH
/Paul
