BookmarkSubscribeRSS Feed
forumsguy
Fluorite | Level 6

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...

5 REPLIES 5
Cynthia_sas
SAS Super FREQ

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

FriedEgg
SAS Employee

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
forumsguy
Fluorite | Level 6

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 ???

art297
Opal | Level 21

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

Ddolic
Calcite | Level 5

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 2400 views
  • 3 likes
  • 5 in conversation