SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Query metadata repository to get information

Reply
Frequent Contributor
Posts: 85

Query metadata repository to get information

Hi All,

My recent task is to identify all attributes of all tables in  Foundation Repository .... I want to display the information of Primary keys, Not null and Indexes particularly... I am able to find this information in one given library for one table but I am confused about getting information about all the tables in repository... Basically, I want the report of all tables with their particular details of my Foundation repository... Any guidance will be really helpful...

SAS Super FREQ
Posts: 8,869

Query metadata repository to get information

Posted in reply to forumsguy

Hi:

  This is not a BASE SAS or Macro question, per se. You might wish to post this question in the Stored Process forum or the Web Report Studio forum, which is where most of the "metadata" people hang out.

cynthia

Trusted Advisor
Posts: 1,301

Query metadata repository to get information

Posted in reply to forumsguy

Just incase your reference to 'metadata' does not actually mean literally querying the metadata server and instead using the SAS/Base dictionary data you collect information about a table, library or group of tables/libraries you could do something along the lines of the following.

proc sql;

create table foobar(label='Some Table?')

  ( name char(8) not null,

    sex char(1),

          age num,

    height num,

          weight num not null,

          constraint prim_key primary key(name),

          constraint sex check(sex in ('M','F')),

          constraint age check(age gt 0),

          constraint height check(height gt 0),

          constraint weight check(weight gt 0)

   );

insert into foobar (name,sex,age,height,weight)

select name,sex,age,height,weight

   from sashelp.class;

select a.libname "Library",

        a.memname "Table Name",

        a.memlabel "Table Label",

        a.indxtype "Index Type",

                    b.name as indxcolname "Index Column Name",

                    b.indxname "Index Name",

                    b.nomiss as indxnomiss "Index NOMISS",

                    b.unique as indxuniq "Index Unique"

   from sashelp.vtable a

   join sashelp.vindex b on a.libname=b.libname and a.memname=b.memname

  where a.libname='WORK' and a.memname='FOOBAR';

select libname "Library",

        memname "Table Name",

        name "Not Null Column Name"

   from sashelp.vcolumn

  where libname='WORK' and memname='FOOBAR' and upcase(notnull)='YES';

select a.table_catalog "Libary",

        a.table_name "Table Name",

        a.constraint_name "Constraint Name",

                    b.column_name "Constraint Column Name",

                    c.check_clause "Constraint Check Clause"

   from sashelp.vcntabu a

   join sashelp.vcncolu b on a.constraint_name=b.constraint_name

   left join sashelp.vchkcon c on a.constraint_name=c.constraint_name

  where a.table_catalog='WORK' and a.table_name='FOOBAR';

quit;

LibraryTable NameTable LabelIndex TypeIndex Column NameIndex NameIndex NOMISSIndex Unique
WORKFOOBARSome Table?SIMPLEnamenamenoyes

LibraryTable NameNot Null Column Name
WORKFOOBARname
WORKFOOBARweight

LibaryTable NameConstraint NameConstraint Column NameConstraint Check Clause
WORKFOOBAR_NM0001_name
WORKFOOBAR_NM0002_weight
WORKFOOBARageageage>0
WORKFOOBARheightheightheight>0
WORKFOOBARprim_keyname
WORKFOOBARsexsexsex in ('F', 'M')
WORKFOOBARweightweightweight>0
Frequent Contributor
Posts: 85

Query metadata repository to get information

Wow... thanks a lot Fried Egg.. but what I meant was actually using Metadata datastep functions.... I have used metadata_resolve, getnasl getattr,and getnasn functions to get these attributes.... @Cynthia... how to move this thread to Stored Process or WRS forum ??? Or else should I start another topic there ???

PROC Star
Posts: 7,492

Query metadata repository to get information

Posted in reply to forumsguy

Renee would be the one who could move it but, given that its a weekend, I would just start another topic there.

Occasional Contributor
Posts: 5

Re: Query metadata repository to get information

Posted in reply to forumsguy

Sorry, I just dump some code fragments:

%macro procmeta(user=DWH,

  passwd={sas001}xxx,

  in=query, /* Filereferenz auf XML Datei mit Metadaten*/

  out=tmpout);

proc metadata

SERVER       = "sas-meta.corp.de"

PORT         = 8561

REPOSITORY   = "KPI"

USERID       = "&user"

PASSWORD     = "&passwd"

IN  = &in

OUT          = &out

;

run;

%mend procmeta;


This Macro can query the metadataserver. For IN use some TXT file like this one:

data _null_;

   file query;

   input;

   put _infile_;

   datalines;

  <GetMetadataObjects>

<Reposid>$METAREPOSITORY</Reposid>

<Type>Tree</Type>

      <Objects>

      <Tree Name="KPI"/>

      </Objects>

<Ns>SAS</Ns>

<Flags/>

<Options>

     <Templates>

       <Physicaltable/>

     </Templates>

    </Options>

  </GetMetadataObjects>

;;

run;

Ask a Question
Discussion stats
  • 5 replies
  • 714 views
  • 3 likes
  • 5 in conversation