Production Ready Macros for SAS Application Developers
https://github.com/sasjs/core
mm_gettables.sas
Go to the documentation of this file.
1 /**
2  @file
3  @brief Creates a dataset with all metadata tables for a particular library
4  @details Will only show the tables to which a user has the requisite
5  metadata access.
6 
7  usage:
8 
9  %mm_gettables(uri=A5X8AHW1.B40001S5)
10 
11  @param outds the dataset to create that contains the list of tables
12  @param uri the uri of the library for which to return tables
13  @param getauth= YES|NO - fetch the authdomain used in database connections.
14  Set to NO to improve runtimes in larger environments, as there can be a
15  performance hit on the `metadata_getattr(domainuri, "Name", AuthDomain)` call.
16 
17  @returns outds dataset containing all groups in a column named "metagroup"
18  (defaults to work.mm_getlibs). The following columns are provided:
19  - tablename
20  - tableuri
21  - libref
22  - libname
23  - libdesc
24 
25  @version 9.2
26  @author Allan Bowe
27 
28 **/
29 
30 %macro mm_gettables(
31  uri=
32  ,outds=work.mm_gettables
33  ,getauth=YES
34 )/*/STORE SOURCE*/;
35 
36 
37 data &outds;
38  length uri serveruri conn_uri domainuri libname ServerContext AuthDomain
39  path_schema usingpkguri type tableuri $256 id $17
40  libdesc $200 libref engine $8 IsDBMSLibname $1
41  tablename $50 /* metadata table names can be longer than $32 */
42  ;
43  keep libname libdesc libref engine ServerContext path_schema AuthDomain tableuri
44  tablename IsPreassigned IsDBMSLibname id;
45  call missing (of _all_);
46 
47  uri=symget('uri');
48  rc= metadata_getattr(uri, "Name", libname);
49  if rc <0 then do;
50  put 'The library is not defined in this metadata repository.';
51  stop;
52  end;
53  rc= metadata_getattr(uri, "Desc", libdesc);
54  rc= metadata_getattr(uri, "Libref", libref);
55  rc= metadata_getattr(uri, "Engine", engine);
56  rc= metadata_getattr(uri, "IsDBMSLibname", IsDBMSLibname);
57  rc= metadata_getattr(uri, "IsPreassigned", IsPreassigned);
58  rc= metadata_getattr(uri, "Id", Id);
59 
60  /*** Get associated ServerContext ***/
61  rc= metadata_getnasn(uri, "DeployedComponents", 1, serveruri);
62  if rc > 0 then rc2= metadata_getattr(serveruri, "Name", ServerContext);
63  else ServerContext='';
64 
65  /*** If the library is a DBMS library, get the Authentication Domain
66  associated with the DBMS connection credentials ***/
67  if IsDBMSLibname="1" and "&getauth"='YES' then do;
68  rc= metadata_getnasn(uri, "LibraryConnection", 1, conn_uri);
69  if rc>0 then do;
70  rc2= metadata_getnasn(conn_uri, "Domain", 1, domainuri);
71  if rc2>0 then rc3= metadata_getattr(domainuri, "Name", AuthDomain);
72  end;
73  end;
74 
75  /*** Get the path/database schema for this library ***/
76  rc=metadata_getnasn(uri, "UsingPackages", 1, usingpkguri);
77  if rc>0 then do;
78  rc=metadata_resolve(usingpkguri,type,id);
79  if type='Directory' then
80  rc=metadata_getattr(usingpkguri, "DirectoryName", path_schema);
81  else if type='DatabaseSchema' then
82  rc=metadata_getattr(usingpkguri, "Name", path_schema);
83  else path_schema="unknown";
84  end;
85 
86  /*** Get the tables associated with this library ***/
87  /*** If DBMS, tables are associated with DatabaseSchema ***/
88  if type='DatabaseSchema' then do;
89  t=1;
90  ntab=metadata_getnasn(usingpkguri, "Tables", t, tableuri);
91  if ntab>0 then do t=1 to ntab;
92  tableuri='';
93  tablename='';
94  ntab=metadata_getnasn(usingpkguri, "Tables", t, tableuri);
95  tabrc= metadata_getattr(tableuri, "Name", tablename);
96  output;
97  end;
98  else put 'Library ' libname ' has no tables registered';
99  end;
100  else if type in ('Directory','SASLibrary') then do;
101  t=1;
102  ntab=metadata_getnasn(uri, "Tables", t, tableuri);
103  if ntab>0 then do t=1 to ntab;
104  tableuri='';
105  tablename='';
106  ntab=metadata_getnasn(uri, "Tables", t, tableuri);
107  tabrc= metadata_getattr(tableuri, "Name", tablename);
108  output;
109  end;
110  else put 'Library ' libname ' has no tables registered';
111  end;
112 run;
113 
114 proc sort;
115 by tablename tableuri;
116 run;
117 
118 %mend;