SPD Server SQL Dictionary Tables

The server provides dictionary tables that enable you to get metadata information about user objects such as tables, columns, indexes, and ACLs. SAS also has dictionary tables, but the SAS dictionary tables and the server dictionary tables are different and cannot be used interchangeably. The server uses some dictionary tables that SAS does not support, such as DICTIONARY.ACLS, DICTIONARY.PWDB, and DICTIONARY.CLUSTERS.

The following dictionary tables are available to a server user with explicit SQL pass-through:

Use the DESCRIBE TABLE statement to print information about the dictionary tables. The DESCRIBE TABLE output is written to the SAS log. Here is a sample output for each dictionary table:

 execute (describe table dictionary.members)    by sasspds; 

SPDS_NOTE: SQL table DICTIONARY.members was created like:  

create table DICTIONARY.members 
 (
  LIBNAME char(8) label='Library Name',     
  MEMNAME char(32) label='Member Name',     
  MEMTYPE char(8) label='Member Type',     
  ENGINE char(8) label='Engine Name',     
  INDEX char(32) label='Indexes',     
  PATH char(1024) label='Pathname'  );    

execute (describe table dictionary.tables) 
  by sasspds;

SPDS_NOTE: SQL table DICTIONARY.tables was created like:
create table DICTIONARY.tables   
  (
   LIBNAME char(8) label='Library Name',   
   MEMNAME char(32) label='Member Name',
   MEMTYPE char(8) label='Member Type',  
   MEMLABEL char(256) label='Data Set Label',  
   TYPEMEM char(8) label='Data Set Type',  
   CRDATE num format=DATETIME informat=DATETIME label='Date Created',  
   MODATE num format=DATETIME informat=DATETIME label='Date Modified',  
   NOBS num label='Number of rows',  
   OBSLEN num label='row Length',  
   NVAR num label='Number of Variables', 
   PROTECT char(3) label='Type of Password Protection',  
   COMPRESS char(8) label='Compression Routine',  
   REUSE char(3) label='Reuse Space',  
   BUFSIZE num label='Bufsize',  
   DELOBS num label='Number of Deleted rows',   
   INDXTYPE char(9) label='Type of Indexes',   
   LOCALE char(32) label='Locale',  
   ENCODING num label='Encoding_Cei'  );    

execute (describe table dictionary.columns) by sasspds;

SPDS_NOTE: SQL table DICTIONARY.columns was created like:  
create table DICTIONARY.columns   
  (     
   LIBNAME char(8) label='Library Name', 
   MEMNAME char(32) label='Member Name',  
   MEMTYPE char(8) label='Member Type',  
   NAME char(32) label='Column Name',  
   TYPE char(4) label='Column Type',   
   LENGTH num label='Column Length',  
   NPOS num label='Column Position',  
   VARNUM num label='Column Number in Table',  
   LABEL char(256) label='Column Label',  
   FORMAT char(16) label='Column Format', 
   INFORMAT char(16) label='Column Informat', 
   IDXUSAGE char(9) label='Column Index Type'  );    

execute(describe table dictionary.indexes) by sasspds;

SPDS_NOTE: SQL table DICTIONARY.indexes was created like:
create table DICTIONARY.indexes
   (
    LIBNAME char(8) label='Library Name',
    MEMNAME char(32) label='Member Name',
    MEMTYPE char(8) label='Member Type',
    NAME char(32) label='Column Name',
    IDXUSAGE char(9) label='Column Index Type',
    INDXNAME char(32) label='Index Name',
    INDXPOS num label='Position of Column in Concatenated Key',
    NOMISS char(3) label='Nomiss Option',
    UNIQUE1 char(3) label='Unique Option'  );
execute (describe table dictionary.views) by sasspds;    

SPDS_NOTE: SQL table DICTIONARY.views was created like:  
create table DICTIONARY.views  
  (  
   LIBNAME char(8) label='Library Name',  
   MEMNAME char(32) label='Member Name',  
   MEMTYPE char(8) label='Member Type',  
   ENGINE char(8) label='Engine Name'  );    

execute (describe table dictionary.acls) by sasspds;   

SPDS_NOTE: SQL table DICTIONARY.acls was created like:  
create table DICTIONARY.acls  
  (  
   LIBNAME char(8) label='Library Name', 
   MEMNAME char(32) label='Member Name', 
   MEMTYPE char(8) label='Member Type',   
   NAME char(32) label='Column Name',  
   OWNER char(8) label='Owner',  
   GROUP char(8) label='Group',   
   DEFACS char(56) label='Default Access',  
   GRPACS char(56) label='Group Access'  );    

execute (describe table dictionary.pwdb) by sasspds;   

SPDS_NOTE: SQL table DICTIONARY.pwdb was created like:  
create table DICTIONARY.pwdb  
  (     
   USER char(8) label='User',  
   AUTH_LVL char(5) label='Authorization Level',   
   IP_ADDR char(16) label='IP Address',  
   DEFGRP char(8) label='Default Group',  
   OTHGRPS char(224) label='Other Groups',   
   EXPIRE char(6) label='Expire Period',  
   MOD_DATE char(32) label='Password Last Modified',  
   LOG_DATE char(32) label='Last Login',   
   TIMEOUT char(8) label='Timeout Period',  
   ALLOWED char(10) label='Failed Login Attempts Allowed',   
   STRIKES char(6) label='Failed Login Attempts'  );

execute (describe table dictionary.clusters) by sasspds;   

SPDS_NOTE: SQL table DICTIONARY.clusters was created like:  
create table DICTIONARY.clusters  
  (  
   LIBNAME char(8) label='Library Name',  
   CLSTNAME char(32) label='Cluster Name',  
   TYPE char(5) label='Cluster Type',  
   MBRNAME char(32) label='Cluster Member',  
   );

execute (describe table dictionary.sysinfo) by sasspds;   

SPDS_NOTE: SQL table DICTIONARY.sysinfo was created like:  
create table DICTIONARY.sysinfo  
  ( 
   SYS_ENC char(32) label='Server Host Default Encoding',   
   SYS_OS char(64) label='Server Operating System',  
   SYS_NAME char(32) label='Server Host Name',  
   SYS_SPDS char(32) label='SPDS Version Number'  );

Use the SELECT statement to return dictionary information about actual resources in a domain. SELECT outputs are written to the SAS Output window using the SAS Output Delivery System. For example, to list the SPD Server resources in the domain Test, submit this SELECT statement:

select * from connection to sasspds
(select * from dictionary.members);

The server returns output similar to the following:

To get information about a specific data table, submit a SELECT statement similar to the following:

select * from connection to sasspds
(select * from dictionary.tables where memname='AUDICARS');

The server returns output similar to the following: