How to: Easily get the list of fields of a database table

[datanibbler]

Hi,

quite regularly you have a LOAD statement with an * which is a bit unpractical and you want a list of all the fields in the table.

(in my instance, I had to construct a new LOAD and I had to do some guessing because I cannot execute LOADs from the database locally, but only via the QMC, so I cannot actually see what fields are in the database_table, so I had to use the *)

In that case, the easiest way to get a list of the fields that you can copy into the script (you cannot copy from the table_viewer) is this:

=> Build a FOR NEXT loop to load into some table the name of all the fields in the table:

   >> LET v_nr_fields = NoOfFields();

          FOR i = 1 TO $(v_nr_fields)

              Temp: FIRST 1 LOAD Fieldname($(i), '[tablename]') as Feldname RESIDENT [tablename];

          NEXT

   <<

Then open the table_viewer where you can view this table and use the "export structure" feature.

=> Open the >Fields.tab< file in Excel

=> Voila, you can export the list of fields and using an Excel_formula, you can easily add the >comma<

Best regards,

DataNibbler

[m_woolf]

If you have loaded a table with *, you can make a list box that shows the system field $Table and a list box that shows $Field.

Then selecting any Table in the first list box will show all the available fields in the second list box.

[colinodonnel]

Yes that loop works nicely.

Had to make a couple of small changes to the script as the table viewer wouldn't export for me.

(note that the brackets are removed from the table name in the formula Fieldname($(i), 'RawData')    )

RawData:

Load  * from QVD;

   LET v_nr_fields = NoOfFields('RawData');

          FOR i = 1 TO $(v_nr_fields)

              Temp: FIRST 1 LOAD Fieldname($(i), 'RawData') as Fieldname RESIDENT [RawData];

          NEXT

Drop table RawData;

Store * from Temp into [lib/temp.csv] (txt);

[colinodonnel]

works perfectly