Victor BA


I need to obtain which tables have a specific field name, like "last name" or "id", because i need to do modifications, and there are around 70 tables, so it would be a nightmare to look the structure of each one, what can i do


Re: same field on several tables

Vladimir Zografski


Try this:

Code Snippet

CLOSE DATABASES ALL
SET DEFAULT TO e:\my_dbfs &&& write here the path to yours dbf's

CREATE CURSOR crsTable (tblName C(20))

SELECT 0

lookfor = UPPER([Code]) &&& Write here your field name

cntDbfs = ADIR(myDbfs,[*.dbf])

FOR loopDbfs = 1 TO m.cntDbfs
USE (myDbfs(m.loopDbfs,1))
cntFields = AFIELDS(ArrFields)
FOR loopvar = 1 TO m.cntFields
IF UPPER(ArrFields(m.loopvar,1)) == m.lookfor
INSERT INTO crsTable VALUES (myDbfs(m.loopDbfs,1))
EXIT
ENDIF
NEXT
NEXT

SELECT crsTable
BROWSE NORMAL
-------------------









Re: same field on several tables

Naomi Nosonovsky

I have a slightly different idea than Vladimir, though I'm not sure which approach would work faster:

Code Snippet

create cursor tableNames (TableName C(30))

lnTables = adir(laTables, myDir + "*.dbf")

for lnI = 1 to lnTables

try

select ID, Code from (laTables[m.lnI,1]) where 1=0 into array laTemp

insert into TableNames values (laTables[m.lnI,1])

catch to loErr

* This table doesn't have these fields

endtry

next






Re: same field on several tables

CetinBasoz

Naomi,

I don't think timing would be an issue and if you needed for multiple fields then probably one wold want to get more information. ie:

LOCAL ix,jx,lcDataDir

lcDataDir = GETDIR()

Create cursor crsSTRUCTS ;

(FIELD_NAME C(128) nocptrans, ;

FIELD_TYPE C(1), ;

FIELD_LEN N(3, 0), ;

FIELD_DEC N(3, 0), ;

FIELD_NULL L, ;

FIELD_NOCP L, ;

_TABLENAME C(128))

Select 0

For ix=1 to adir(arrTables,ADDBS(m.lcDataDir)+'*.dbf')

Use (ADDBS(m.lcDataDir)+arrTables[ix,1])

For jx=1 to afields(arrStruc)

arrStruc[m.jx,7]=arrTables[m.ix,1]

Endfor

Insert into crsSTRUCTS from array arrStruc

Release arrStruc

Use

ENDFOR

Select * FROM crsSTRUCTS ORDER BY field_name,_tablename





Re: same field on several tables

Naomi Nosonovsky

Yes, this is useful. I was just trying to solve the original problem.