I want to list all tables on metadata server. I've tried with the following data step but it only pics one table per library and I can't figure out why.
This is the code I've been using:
options metaserver="xxxx"
metaport=8561
metauser="xxxx"
metapass="xxxx"
METAPROTOCOL=BRIDGE
metarepository="Foundation";
data meta_libraries;
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 colname coltype collen $32;
keep libname desc libref engine ServerContext path_schema AuthDomain table colname coltype collen
IsPreassigned IsDBMSLibname id;
nobj=.;
n=1;
uri='';
serveruri='';
conn_uri='';
domainuri='';
/***Determine how many libraries there are***/
nobj=metadata_getnobj("omsobj:SASLibrary?@Id contains '.'",n,uri);
/***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=metadata_getnobj("omsobj:SASLibrary?@Id contains '.'",n,uri);
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 do;
put 'Library ' libname ' has no tables registered';
output;
end;
end;
end;
else if type='Directory' 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;
/***If there aren't any libraries, write a message to the log***/
else put 'There are no libraries defined in this metadata repository.';
run;
I've would appreciate if someone has any suggestions?
I would recommend you to use "proc metadata" instead of the data step functions.
This is a possible solution although it can be improved since it is bringing more information than it should.
options metaport = &metaport
metaserver = "&metaserver"
metarepository = "Foundation"
metauser = &meta_user.
metapass = "&meta_pass";
* use a temporary fileref to hold the request;
filename request temp;
* use a data step to create the XML request;
data _null_;
file request;
put '<GetMetadataObjects>';
put ' <Reposid>$METAREPOSITORY</Reposid>';
put ' <Type>PhysicalTable</Type>';
put ' <Objects />';
put ' <NS>SAS</NS>';
* Flags: <!-- OMI_ALL(1) + OMI_GET_METADATA(256) + OMI_TEMPLATE (4)+ OMI_SUCCINT (2048) = 2309 -->;
put ' <Flags>2309</Flags>';
put '<Options>';
put ' <Templates>';
put ' <PhysicalTable/>';
put ' <SASLibrary Engine="" Libref=""/>';
put ' </Templates>';
put ' </Options>';
put '</GetMetadataObjects>';
run;
* use a temporary fileref to hold the response;
filename response TEMP; *"%sysfunc(getoption(work))/response.xml";
proc metadata in=request out=response verbose;
run;
* release the temporary request;
filename request;
filename mapa temp;
data _null_;
file mapa;
put '<?xml version="1.0" encoding="windows-1252"?>';
put '<!-- ############################################################ -->';
put '<!-- 2014-02-05T15:41:54 -->';
put '<!-- SAS XML Libname Engine Map -->';
put '<!-- Generated by XML Mapper, 903200.3.0.20120523190000_v930m2 -->';
put '<!-- ############################################################ -->';
put '<SXLEMAP name="AUTO_GEN" version="2.1">';
put ' <NAMESPACES count="0"/>';
put ' <!-- ############################################################ -->';
put ' <TABLE description="SASLibrary" name="SASLibrary">';
put ' <TABLE-PATH syntax="XPath">/GetMetadataObjects/Objects/PhysicalTable/TablePackage/SASLibrary</TABLE-PATH>';
put ' <COLUMN name="LibId">';
put ' <PATH syntax="XPath">/GetMetadataObjects/Objects/PhysicalTable/TablePackage/SASLibrary/@Id</PATH>';
put ' <TYPE>character</TYPE>';
put ' <DATATYPE>string</DATATYPE>';
put ' <LENGTH>17</LENGTH>';
put ' </COLUMN>';
put ' <COLUMN name="Engine">';
put ' <PATH syntax="XPath">/GetMetadataObjects/Objects/PhysicalTable/TablePackage/SASLibrary/@Engine</PATH>';
put ' <TYPE>character</TYPE>';
put ' <DATATYPE>string</DATATYPE>';
put ' <LENGTH>4</LENGTH>';
put ' </COLUMN>';
put ' <COLUMN name="Libref">';
put ' <PATH syntax="XPath">/GetMetadataObjects/Objects/PhysicalTable/TablePackage/SASLibrary/@Libref</PATH>';
put ' <TYPE>character</TYPE>';
put ' <DATATYPE>string</DATATYPE>';
put ' <LENGTH>8</LENGTH>';
put ' </COLUMN>';
put ' <COLUMN name="TabID" retain="YES">';
put ' <PATH syntax="XPath">/GetMetadataObjects/Objects/PhysicalTable/@Id</PATH>';
put ' <TYPE>character</TYPE>';
put ' <DATATYPE>string</DATATYPE>';
put ' <LENGTH>17</LENGTH>';
put ' </COLUMN>';
put ' <COLUMN name="Name_Table" retain="YES">';
put ' <PATH syntax="XPath">/GetMetadataObjects/Objects/PhysicalTable/@Name</PATH>';
put ' <TYPE>character</TYPE>';
put ' <DATATYPE>string</DATATYPE>';
put ' <LENGTH>32</LENGTH>';
put ' </COLUMN>';
put ' <COLUMN name="SASTableName" retain="YES">';
put ' <PATH syntax="XPath">/GetMetadataObjects/Objects/PhysicalTable/@SASTableName</PATH>';
put ' <TYPE>character</TYPE>';
put ' <DATATYPE>string</DATATYPE>';
put ' <LENGTH>32</LENGTH>';
put ' </COLUMN>';
put ' <COLUMN name="TableName" retain="YES">';
put ' <PATH syntax="XPath">/GetMetadataObjects/Objects/PhysicalTable/@TableName</PATH>';
put ' <TYPE>character</TYPE>';
put ' <DATATYPE>string</DATATYPE>';
put ' <LENGTH>32</LENGTH>';
put ' </COLUMN>';
put ' </TABLE>';
put '</SXLEMAP>';
run;
libname fim xmlv2 xmlfileref=response xmlmap=MAPA access=READONLY;
/*
* Local Extraction
*/
DATA SASLibrary; SET fim.SASLibrary; run;
* release the temporary file and libname;
filename response clear;
libname fim clear;
Your else if type='Directory' then do; line was outside of the necessary do; end; group.
Other than that, your code runs fine! See below.
data meta_libraries;
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 colname coltype collen $32;
keep libname desc libref engine ServerContext path_schema AuthDomain table colname
coltype collen IsPreassigned IsDBMSLibname id;
nobj=.;
n=1;
uri='';
serveruri='';
conn_uri='';
domainuri='';
/***Determine how many libraries there are***/
nobj=metadata_getnobj("omsobj:SASLibrary?@Id contains '.'",n,uri);
/***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=metadata_getnobj("omsobj:SASLibrary?@Id contains '.'",n,uri);
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 do;
put 'Library ' libname ' has no tables registered';
output;
end;
end;
else if type='Directory' 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;
proc sort; by libref table;run;
