At 06:05 PM 7/30/2007, Patrick Murphy wrote:
Does anyone have some code that can be used to export all column
names for a table to CSV file? I need an automatic method, in lieu
of the "Choose vcolumns FROM #columns..." and "Shift-F6" method.
Pat,
Here's how:
01. Find the SYS_TABLE_ID for a known/given table name.
Example:
SELECT SYS_TABLE_ID INTO vSYS_TABLE_ID INDIC ivSYS_TABLE_ID +
FROM SYS_TABLES WHERE SYS_TABLE_NAME = 'tablename'
02. Now select the list of all columns for a given SYS_TABLE_ID
Example:
SELECT (LISTOF(SYS_COLUMN_NAME)) INTO vColList INDIC ivColList +
FROM SYS_COLUMNS WHERE SYS_TABLE_ID = .vSYS_TABLE_ID
This will return a list of all columns separated by comma.
03. Use this technique to send the output to a .CSV file.
Example:
OUTPUT filename.CSV
SET WIDTH 400
SET SELMARGIN 1
SELECT (LISTOF(SYS_COLUMN_NAME)) FROM SYS_COLUMNS WHERE +
SYS_TABLE_ID = .vSYS_TABLE_ID
OUTPUT SCREEN
04. Use LAUNCH command to open CSV file that only include the
list of columns for a given table.
05. Based on the version of R:BASE, define your own routines
to automate these techniques/ideas to achieve your goal.
Hope that helps!
Very Best R:egards,
Razzak.
