Unix List Oracle Tables and Columns

Listing the tables and columns of an Oracle database has many applications. I have created a SAS front end using VBA code to select the tables and columns by highliting forms.

The macro, Download_Export, can download the Tables and Columns SAS files to your P/C.

 

SAS Fifth Dimension Macro-TablesColumns

%MACRO Tables_columns(dir) ;

Parameter Description
DIRECTORY The Unix directory to save the SAS files with tables and columns

Example

Assumptions: This macro is executed in the Unix environment.

%MACRO Tables_columns(dir) ;
libname dir "&dir" ;
proc sql ;
describe table dictionary.tables ;
create table dir.tables as
select memname, nobs, nvar, modate from dictionary.tables
where memname ^= "" and nobs =. and modate=. ; ;
quit ;
proc sql;
describe table dictionary.tables ;
create table dir.columns as
select * from dictionary.COLUMNS
where name ^= '' and UPCASE(libname) ^="SASUSER"
and UPCASE(libname) ^="SASHELP" and UPCASE(libname) ^="WORK";
quit;
%MEND Tables_columns ;