Get table properties of a given database

[Serge]

Hi All,

How can I get all fields of all databases?

This procedure gives me properties of all connected "tables":

FOR EACH _file NO-LOCK WHERE _file._hidden = NO:
DISPLAY _file WITH 1 COL WIDTH 200.
END.

Can't I do something like this example:

FOR EACH _Db,
EACH _File NO-LOCK WHERE _File.hidden = NO:

DISPLAY _Db _File WITH 1 COL WIDTH 200.
END.

--> this is NOT working

Thanks in advance!

 

[TomBascom]

You need to do it in two steps with two programs using the DICTDB alias.

Something along these lines (I haven't tested this):

for each _db no-lock:
  dictdb = _db-name.
  run prog2.p.
end.

Where prog2 is your original program. You need two programs because you cannot set an alias and use the new value in the same program.

 

[Serge]

I tried already this:

for each _db no-lock:
display _db-name.
end.

I'm sure my procedure editor is connected with 4 different databases and this procedure gives me just 1 output with _db-name = ?

???

 

[hakane]

Hi Serge,

try this:

DEFINE VARIABLE i AS INTEGER.
REPEAT i = 1 TO NUM-DBS:
DISPLAY LDBNAME(i) FORMAT "x(40)".
END.

 

[Serge]

Thanks Hakane,

but my initial question was how to get table names of each database?

with your procedure I know which databases there are but still not wchich tables are attached to it ...

 

[Casper]

This a dynamic example on how you could do this:

[SIZE=2][COLOR=#7f0055][SIZE=2][COLOR=#000000]DEFINE VARIABLE iDb AS INTEGER NO-UNDO.[/COLOR][/SIZE]
[SIZE=2][COLOR=#7f0055]DEFINE VARIABLE hQuery AS HANDLE NO-UNDO.[/COLOR][/SIZE]
[SIZE=2][COLOR=#7f0055]DEFINE VARIABLE hBuffer AS HANDLE NO-UNDO.[/COLOR][/SIZE]
 
[SIZE=2][COLOR=#7f0055]do iDb = 1 to num-dbs:[/COLOR][/SIZE]
[SIZE=2][COLOR=#7f0055]   create query hQuery.[/COLOR][/SIZE]
[SIZE=2][COLOR=#7f0055]   create buffer hBuffer for table ldbname(iDb) + '._file'.[/COLOR][/SIZE]
[SIZE=2][COLOR=#7f0055]   hQuery:set-buffers(hBuffer).[/COLOR][/SIZE]
[SIZE=2][COLOR=#7f0055]   hQuery:query-prepare('for each ' + ldbName(iDb) + '._file where ' +  ldbName(iDb) + '._file._hidden = no no-lock').[/COLOR][/SIZE]
[SIZE=2][COLOR=#7f0055]   hQuery:query-open().[/COLOR][/SIZE]
[SIZE=2][COLOR=#7f0055]   hQuery:get-first().[/COLOR][/SIZE]
 
[SIZE=2][COLOR=#7f0055]   do while not hQuery:query-off-end:[/COLOR][/SIZE]
[SIZE=2][COLOR=#7f0055]      display hQuery:get-buffer-handle(1):buffer-field("_file-name"):buffer-value. [/COLOR][/SIZE]
[SIZE=2][COLOR=#7f0055]      hQuery:get-next().[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#7f0055][SIZE=2][COLOR=#000000]   end.[/COLOR][/SIZE]
[SIZE=2][COLOR=#7f0055]   [/COLOR][/SIZE]
[SIZE=2][COLOR=#7f0055]   hQuery:query-close().[/COLOR][/SIZE]
[SIZE=2][COLOR=#7f0055]   delete object hBuffer.[/COLOR][/SIZE]
[SIZE=2][COLOR=#7f0055]   delete object hQuery.[/COLOR][/SIZE]
[SIZE=2][COLOR=#7f0055]end.[/COLOR][/SIZE][/COLOR][/SIZE]

HTH,
Casper.

 

[sphipp]

This works for all connected databases:

Program mktbl1.p.

def var this_loop as int no-undo.
do this_loop = 1 to num-dbs:
  display this_loop ldbname (this_loop).
  create alias dictdb for database value (ldbname (this_loop)).
  run mktbl2.p.  
end.

Program mktbl2.p.

for each dictdb._file, each dictdb._field of dictdb._file 
  break by dictdb._file._file-name by dictdb._field._order:
  display 
    dictdb._file._file-name 
    dictdb._field._field-name 
    with frame f1 1 down side-labels.
 
  /* Do whatever you want with these records */
 
end.

 

[sgs.prasad]

Nice code from sphipp

 

[Casper]

You mean you dont like my dynamic example, its shorter, does the same and its only 1 program .

casper.

 

[sgs.prasad]

Casper,

It is not like that, your program also works fine but it was giving list of only table names of all connected database. Actually I wanted to see list of field names for all tables for all databases which are connected.
I came to know about .field concept through above code.

Prasad

 

[sphipp]

You mean you dont like my dynamic example, its shorter, does the same and its only 1 program .

casper.

But doesn't work in version 8, or 7, or 6 .....

 

[Casper]

But doesn't work in version 8, or 7, or 6 .....

Another reason to upgrade form such an ancient version

 

[budfrog532]

Casper and sphipp,
Thanks for your answers here. I needed to be able to run Pro2My schema dumps against 6 different databases, and this little hint allowed me to set that up perfectly.

Just wanted to say thanks for the help -- I know people tend to not post back when a message board post has helped them out.

Cheers.