I'm querying several tables from Oracle and am wondering if there is a procedure out there that will tell me which Oracle tables my extracted fields are coming from. When reading the source code in SAS I can obviously see the names of the Oracle tables that are being used to extract my data, but I am wondering if there is a way to determine this programmatically.
If you're wondering what all of this is for -- I'm trying to write a procedure that will essentially create a report inventory so I will be able to determine which Oracle tables/columns are being used on which reports.
most data bases have their own internal dictionary tables.
In SAS it is called dictionary.columns or sashelp.vcolumn for the metadata about columns; dictionary.members and dictionary.tables for metadata about tables and dictionary.indexes, dictionary..... many other things.
Although your SAS session (once connected to the data base with a libname statement) will be able to provide most of what you need in dictionary.columns, you may find a more useful collection of metadata (and faster) in your database's own system tables.
If you do want to use the metadata in the Oracle system tables, as Peter suggested, you can view this data from within SAS by assigning a libname to the database with “SYS” as the schema, provided that your DBA has given you the appropriate permissions to view the system tables.