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