Generic way to get SAS Table metadata URI

Question

I'm building a utility that leverages the SAS metadata ID (or URI) of a table object. The following code works fine for getting the ID when the library uses the BASE engine:

%let mylib=SOMELIB;
data output (keep=uri dataname);
  length uri $100 dataname $256;
  uri='';
  i=1;
  do until (rc<0);
    rc=metadata_getnasn("omsobj:SASLibrary?@Libref='&mylib'","Tables",i,uri);
    put rc=;
    prc=metadata_getattr(uri,"Name",dataname);
    if rc>=0 then output;
    i+1;
    put i=;
  end;
run;

However for other library engines (eg OLEDB, ODBC, REMOTE) SAS will store the information in different properties (eg under "UsingPackages/[my db]/Tables"). I can write conditional logic for each of the library engines I come across, but wondered if there was an easier / more generic way to get the Table ID?

The same issue occurs in reverse (if I search for the Table I still need the SASLibrary to ensure it is unique).

Answer 1

The code in this article was extremely helpful for our SAS admins:

http://support.sas.com/documentation/cdl/en/lrmeta/63180/HTML/default/viewer.htm#p1k9zipe59ha2an1pq34gu143lay.htm

Example: Listing Libraries and Their Server Contexts

This program uses the SAS metadata DATA step functions to return more detailed information about the libraries. The results are returned to a Libraries data set in the Work library. The requested data includes the library metadata ID, the library name, libref, engine, path on the file system (or if DBMS data, the DBMS path), and the server contexts to which the library is associated.

It'll give you everything you want and more.

Answer 2

This is the code I used in the end (inspired from this post). It also returns the metadata folder path for each table. It doesn't have 100% coverage of the estate, but is fine for my utility. I'd also point out that it uncovered a handful of instances where we had more than one table (with the same name) registered with the same library. Worth having in the test harness!

%macro get_metadata_library_tables(
    libref= /* supply a libref here if filtering for a single library.
                Leaving this blank will return ALL tables */
    ,outds= metadata_tables /* name of output dataset to create */ 
  );

%local nobj_statement;
%if %length(&libref)=0 %then %let nobj_statement=
  %str( metadata_getnobj("omsobj:SASLibrary?@Id contains '.'",n,uri) );
%else %let nobj_statement=
  %str( metadata_getnobj("omsobj:SASLibrary?@Libref='&libref'",n,uri) );

data &outds;
  length uri serveruri conn_uri domainuri libname ServerContext AuthDomain 
    path_schema usingpkguri type tableuri coluri $256 id $17
    desc $200 libref engine $8 isDBMS $1 
    table $50 /* metadata table names can be longer than $32 */
    ;
  keep libname desc libref engine ServerContext path_schema AuthDomain tableuri 
    table IsPreassigned IsDBMSLibname id;
  nobj=.;
  n=1;
  uri='';
  serveruri='';
  conn_uri='';
  domainuri='';

  /***Determine if library/ies exist ***/
  nobj=&nobj_statement;

  /***Retrieve the attributes for all libraries, if there are any***/
  if n>0 then do n=1 to nobj;
    libname='';
    ServerContext='';
    AuthDomain='';
    desc='';
    libref='';
    engine='';
    isDBMS='';
    IsPreassigned='';
    IsDBMSLibname='';
    path_schema='';
    usingpkguri='';
    type='';
    id='';
    nobj=&nobj_statement;
    rc= metadata_getattr(uri, "Name", libname);
    rc= metadata_getattr(uri, "Desc", desc);
    rc= metadata_getattr(uri, "Libref", libref);
    rc= metadata_getattr(uri, "Engine", engine);
    rc= metadata_getattr(uri, "IsDBMSLibname", isDBMS);
    rc= metadata_getattr(uri, "IsDBMSLibname", IsDBMSLibname); 
    rc= metadata_getattr(uri, "IsPreassigned", IsPreassigned); 
    rc= metadata_getattr(uri, "Id", Id);

    /*** Get associated ServerContext ***/
    i=1;
    rc= metadata_getnasn(uri, "DeployedComponents", i, serveruri);
    if rc > 0 then rc2= metadata_getattr(serveruri, "Name", ServerContext);
    else ServerContext='';

    /*** If the library is a DBMS library, get the Authentication Domain
         associated with the DBMS connection credentials ***/
    if isDBMS="1" then do;
      i=1; 
      rc= metadata_getnasn(uri, "LibraryConnection", i, conn_uri);
      if rc > 0 then do;
        rc2= metadata_getnasn(conn_uri, "Domain", i, domainuri);
        if rc2 > 0 then rc3= metadata_getattr(domainuri, "Name", AuthDomain);
      end;
    end;

    /*** Get the path/database schema for this library ***/
    rc=metadata_getnasn(uri, "UsingPackages", 1, usingpkguri);
    if rc>0 then do;
      rc=metadata_resolve(usingpkguri,type,id);  
      if type='Directory' then 
        rc=metadata_getattr(usingpkguri, "DirectoryName", path_schema);
      else if type='DatabaseSchema' then 
        rc=metadata_getattr(usingpkguri, "Name", path_schema);
      else path_schema="unknown";
    end;

    /*** Get the tables associated with this library ***/
    /*** If DBMS, tables are associated with DatabaseSchema ***/
    if type='DatabaseSchema' then do;
      t=1;
      ntab=metadata_getnasn(usingpkguri, "Tables", t, tableuri);
      if ntab>0 then do t=1 to ntab;
        tableuri='';
        table='';
        ntab=metadata_getnasn(usingpkguri, "Tables", t, tableuri);
        tabrc= metadata_getattr(tableuri, "Name", table);
        output;
      end;
      else put 'Library ' libname ' has no tables registered';
    end;
    else if type in ('Directory','SASLibrary') then do;
      t=1;
      ntab=metadata_getnasn(uri, "Tables", t, tableuri);
      if ntab>0 then do t=1 to ntab;
        tableuri='';
        table='';
        ntab=metadata_getnasn(uri, "Tables", t, tableuri);
        tabrc= metadata_getattr(tableuri, "Name", table);
        output;  
      end;
      else put 'Library ' libname ' has no tables registered'; 
    end;
  end;
  /***If there aren't any libraries, write a message to the log***/
  else put 'There are no libraries defined in this metadata repository.'; 
 run;

 /*Find full metadata paths for input objects*/
data &outds;
  set &syslast;
  length tree_path $500 tree_uri parent_uri parent_name $200;
  call missing(tree_path,tree_uri,parent_uri,parent_name);
  drop tree_uri parent_uri parent_name rc rc_tree;

  rc=metadata_getnasn(tableuri,"Trees",1,tree_uri);
  rc=metadata_getattr(tree_uri,"Name",tree_path);

  rc_tree=1;
  do while (rc_tree>0);
    rc_tree=metadata_getnasn(tree_uri,"ParentTree",1,parent_uri);
    if rc_tree>0 then do;
      rc=metadata_getattr(parent_uri,"Name",parent_name);
      tree_path=strip(parent_name)||'/'||strip(tree_path);
      tree_uri=parent_uri;
    end;
  end;
  tree_path='/'||strip(tree_path);
run;

%mend;