Using Windows PowerShell to find registered tables and columns in SAS metadata

This post was kindly contributed by The SAS Dummy - go there to comment and to read the full post.

In a previous post, I described how to write a Windows PowerShell script to connect to a SAS Metadata Server. In this post, I’ll show an example of something useful that you can do after you’ve established that connection. Specifically, I’ll show how to “ask” the SAS Metadata Server about the registered SAS libraries, tables, and data columns.

Beginning with the end in mind

I wanted an easy method to “script” a query to the SAS Metadata Server to retrieve a list of all of the registered data tables and columns. Many SAS applications rely on these registered tables to surface data to end users before making expensive connections to a database to retrieve the “live data”, so it can be useful to know the metadata view of things. From a SAS session, you can use PROC METALIB, the META libname engine, and perhaps PROC METADATA to gather this information. In fact, I found this example in the SAS documentation, which shows how to use PROC METADATA and the GetMetadataObjects method to retrieve most of the details I need.

But what if I want to script this outside of a SAS session and feed the result directly to a CSV file for auditing or review?

Ultimately, I wanted to be able to run a script such as:

./SasMetadataGetColumns.ps1 | 
   Export-CSV -Path 'C:\mydata\regtables.csv' -NoTypeHeader

Alternatively, I could pipe the output to the PowerShell grid viewer, as shown here:

In my previous post, I covered how to:

use SAS Object Manager to define a SAS metadata connection and get a “live” connection (returned as an Open Metadata Interface object — or OMI — in PowerShell)
use the OMI connection GetRepositories method to retrieve the list of metadata repositories
use Windows PowerShell to parse the XML-formatted list of repositories and find the ID of the “Foundation” repository

Adapting PROC METADATA examples for use with PowerShell

Picking up where I left off, I can now use the OMI GetMetadataObjects method to ask for information about the registered tables in metadata. An XML template and a numeric flag value tell the method what level of detail I want. I adapted these values from the PROC METADATA example that I mentioned earlier. (Note: the backwards tick marks you see in these strings are the PowerShell escape character, which allows me to include the double-quotes as part of the XML template string that I defined for use in this query.)

$libTemplate = 
     "<Templates>" +
     "<PhysicalTable/>" +
        "<Column SASColumnName=`"`" SASColumnType=`"`" SASColumnLength=`"`"/>" +
        "<SASLibrary Name=`"`" Engine=`"`" Libref=`"`"/>" +
    "</Templates>"
    
$libs=""

# Use GetMetadataObjects method
# Usage is similar to PROC METADATA, so you
# can look at PROC METADATA doc to get examples
# of templates and queries

# 2309 flag plus template gets table name, column name,  
# engine, libref, and object IDs. The template specifies 
# attributes of the nested objects.                      

$rc = $objOMI.GetMetadataObjects(
      $foundationId, 
      "PhysicalTable", 
      [ref]$libs, 
      "SAS",  
      2309,          # flag with options for this query
      $libTemplate
  )

The output ends up in the $libs object as a large string of XML. As I did with the GetRepositories XML result, I can use PowerShell to parse the XML into into a collection of objects that is simple to navigate:

 # parse the results as XML
[xml]$libXml = $libs

Write-Host "Total tables discovered: " $libXml.Objects.PhysicalTable.Count

Using PowerShell to transform the result

From there, I can use PowerShell to transform the useful nuggets of data into a new structure, which can be easily piped to a CSV file or the PowerShell grid viewer:

# Create output, which you can pipe to another cmdlet
# such as Out-Gridview or Export-CSV

# for each column in each table, create an output object
# (named $objCol here)
for ($i=0; $i -lt $libXml.Objects.PhysicalTable.Count; $i++)
{
  $table = $libXml.Objects.PhysicalTable[$i]
  for ($j=0; $j -lt $table.Columns.Column.Count ; $j++)
  {
    $column = $table.Columns.Column[$j]
    $objCol = New-Object psobject
    $objCol | add-member noteproperty 
      -name "Libref" -value $table.TablePackage.SASLibrary.Libref
    $objCol | add-member noteproperty 
      -name "Table" -value $table.SASTableName
    $objCol | add-member noteproperty 
      -name "Column" -value $column.SASColumnName
    $objCol | add-member noteproperty 
      -name "Type" -value $column.SASColumnType
    $objCol | add-member noteproperty 
      -name "Length" -value $column.SASColumnLength
    
    # emit the object to stdout or other cmdlet
    $objCol
  }

The full code example is available here (on GitHub).

This same approach can be used for other metadata-related queries, such as gathering a list of SAS servers, SAS stored processes, and even users and groups. You can find inspiration and guidance in the many SAS conference papers that feature PROC METADATA. Remember that the metadata that you retrieve, even by using PowerShell, will be limited to what you can see as the SAS metadata account that you use to sign in.

Beginning with the end in mind

Adapting PROC METADATA examples for use with PowerShell

Using PowerShell to transform the result

Related links

Post navigation