Seeking database structure info in a .prg

[youwannawhat]

My user needs to build what amounts to a data dictionary, defining every field in a database for one of our apps. She's asked if I can provide her with a list of the fields in each of the 12 tables that make up the .dbc.

What's the easiest way to do this programmatically?

I have placed the table names in a table. I want to scroll through that table and store the fieldnames of these tables to variables, then display the fieldnames in a report. I would also like to provide her with the unique values for each field(i.e. for a 'reaction' field, I'd like to tell her that their are four choices that have been entered among the 100,000 records-MILD, MEDIUM, SEVERE, NONE) so that she will be better able to recognize them, as the fieldnames can be non-descript.

Thanks for your help in advance.

Paul

 

[ramani]

HI

SELECT IIF(parentId=1,ObjectId,ParentId) AS myObject, ;
IIF(parentId=1,"Table","Field&quot AS myType, ObjectName ;
FROM DATA\GL.DBC INTO DBF test ORDER BY 1
BROWSE
This you can list or print as you want. Suitably replace DATA\GL.DBC with your database name. ramani
(Subramanian.G),FoxAcc, ramani_g@yahoo.com

 

[Mike Gagnon]

If you want I have a "Database analyzer" that allows you to print out a database structure, tables, and indexes. If you want it just give me your e-mail address,

 

[dgrewe]

*/***************************************************************************
*/Program : SYS_DICT.PRG
*/System :
*/Purpose : Create a generic data dictionary program
*/Syntax : =SYS_DICT() or =SYS_DICT("c:\xxx\xxx&quot
*/Returns : nothing
*/Parameter : DIR - string - Directory to build the data dictionary in
*/Defaults : dir = sys(5)+sys(2003)
*/Requires : nothing
*/Changes : SYS_DICT.DBF
*/Version : 1.0
*/Dated : 23 May 1995
*/Written By: David W. Grewe
*/***************************************************************************
*& Utility - Programing
*/***************************************************************************
*/ Record Of Change
*/
*/***************************************************************************
*/ Program standards:
*/ FoxPro Command Words - lower case
*/ Memory Variables & UDFs - UPPER CASE
*/ Memory variable naming method:
*/ F_ (Global - File) Holds Info About The User.
*/ C_ (Global - Constant) once defined they are never changed.
*/ G_ (Global - Public ) may be accessed/changed by any file or procedure.
*/ P_ (Private - used to pass values as parameters to and from procedures.
*/ L_ (Private - used only inside a file or procedure.
*/***************************************************************************
parameter pcSearchPath
if parameters() < 1 or type(&quot;pcSearchPath&quot # &quot;C&quot;
pcSearchPath = sys(5)+sys(2003)
endif
*
set default to (pcSearchPath) && Shift default to work area

set step on

if sys(5)+sys(2003) != pcSearchPath
wait window proper(pcSearchPath) + chr(13) + &quot;is not a valid path !!&quot;
on error
set defa to &lcStartDir
return
endif
*
if right(pcSearchPath,1) != &quot;\&quot;
pcSearchPath = pcSearchPath + &quot;\&quot;
endif
*
if upper(pcSearchPath) = sys(2004)
set defa to &lcStartDir
wait window &quot;FoxPro launch directory &quot; + chr(13) + ;
proper(sys(2004)) + &quot; is not available !!&quot;
on error
return
endif
*
* set enviroment
*
close all
clear
set sysmenu on
set sysmenu to default
set exclu off
set exact on
set talk off
set exclusive off
set safety off
on error **
*
* declare memvars
*
lnDbfs=0
lnFields=0
lcStartDir = sys(5)+sys(2003)
lcDataDbf=pcSearchPath + &quot;SYS_DICT.DBF&quot;
lcDataCdx=pcSearchPath + &quot;SYS_DICT.CDX&quot;
lcDataFpt=pcSearchPath + &quot;SYS_DICT.FPT&quot;
*
* Open/Create Databases
*
if file(lcDataDbf)
use (lcDataDbf) order table alias SYS_DICT exclu
copy stru to C:\TEMP
select 0
use C:\TEMP exclu
else
return
endif
set order to table
*
* Test for dbf files in specified path
*
lnDbfs=adir(laDbfs,&quot;*.DBF&quot
if lnDbfs < 1
wait window proper(pcSearchPath) + chr(13) + &quot;does not contain databases !!&quot;
else
*
* ran out of reasons not to do it so here goes
*
=asort(laDbfs)
@ 1,1 say &quot;Databases To Place In Dictionary&quot;
@ 2,12 say lnDbfs
@ 3,1 say &quot;Completed&quot;
for I = 1 to lnDbfs
@ 3,12 say I
@ 4,12 say padr(laDbfs(I,1),15)
do case
case upper(laDbfs(I,1))=&quot;SYS_DICT.DBF&quot;
loop
case upper(laDbfs(I,1))=&quot;FOXUSER.DBF&quot;
loop
case substr(upper(laDbfs(I,1)),1,4)=&quot;TEMP&quot;
loop
case substr(upper(laDbfs(I,1)),1,3)=&quot;DWG&quot;
loop
endcase
*
* put field structure into to an array
*
lnError=0
on error lnError=ERRORTRAP()
select 0
use laDbfs(I,1) alias IMPORT shared
if lnError < 0
on error
loop
endif
on error
copy stru extended to c:\stru
use
select temp
zap
append from c:\stru
replace all TABLE with strtran(laDbfs(I,1),&quot;.DBF&quot;,&quot;&quot
goto top
*
* populate the data database
*
scan all
scatter memvar memo
if !seek(M.TABLE+M.FIELD_NAME,&quot;SYS_DICT&quot
insert into SYS_DICT from memvar
endif
endscan
*
select TEMP
zap
endfor
*
endif
close databases
delete file (&quot;C:\TEMP.DBF&quot
delete file (&quot;C:\TEMP.CDX&quot
delete file (&quot;C:\TEMP.FPT&quot
delete file (&quot;C:\STRU.DBF&quot
delete file (&quot;C:\STRU.CDX&quot
delete file (&quot;C:\STRU.FPT&quot
set defa to &lcStartDir && Restore user default
release array laFields,laDbfs
release all
set sysmenu to default
on error
clear
return

*!*****************************************************************************
*!
*! Procedure: ERRORTRAP
*!
*! Called by: DICTDATA.PRG
*!
*!*****************************************************************************
procedure ERRORTRAP
*******************
return -1
*: EOF: DICTDATA.PRG
David W. Grewe
Dave@internationalbid.com

 

[dgrewe]

If you want a program that will get th cdx structure of the tables, see

http://ibs.internationalbid.net/home/dave/code_html/cdxstru.html

You can also review

http://ibs.internationalbid.net/home/dave/code_html/sys_lib.html

David W. Grewe
Dave@internationalbid.com

 

[baltman]

The 1st step, getting the field names is simple... select a table and issue &quot;copy struct extended to TargetTable_Struct&quot;...

You could then scan thru each &quot;output&quot; file, grab out the field names and run a loop like:

&quot;select top 10 TargetField,sum(1) as count group by 1 order by 2 into table TargetField_top10 from TargetTable&quot;

I assume you get the idea!

Brian

 

[Uncanny]

Here's another method for getting table names in a database and the field names from each table.

*Sample Code*
Open Database MYDATABASE
nDBFs=ADBOBJECTS(aDBFs,&quot;TABLE&quot
For nI = 1 to nDBFs
?&quot;Table Name = &quot;+aDBFs(nI)
Use (aDBFs(nI))
nFlds = AFields(aFlds)
?&quot;Fields:&quot;
For nX = 1 to nFlds
?&quot; &quot;+aFlds(nX,1)
EndFor
Endfor
*End Sample Code*

You could format the data and store the info into a Variable and to a StrtoFile(cString,cFilename) for a TEXT file output, or you could create a table to store the data.

Have fun.