Production Ready Macros for SAS Application Developers
https://github.com/sasjs/core
mp_getconstraints.sas
Go to the documentation of this file.
1 /**
2  @file mp_getconstraints.sas
3  @brief Get constraint details at column level
4  @details Useful for capturing constraints before they are dropped / reapplied
5  during an update.
6 
7  proc sql;
8  create table work.example(
9  TX_FROM float format=datetime19.,
10  DD_TYPE char(16),
11  DD_SOURCE char(2048),
12  DD_SHORTDESC char(256),
13  constraint pk primary key(tx_from, dd_type,dd_source),
14  constraint unq unique(tx_from, dd_type),
15  constraint nnn not null(DD_SHORTDESC)
16  );
17 
18  %mp_getconstraints(lib=work,ds=example,outds=work.constraints)
19 
20  @param lib= The target library (default=WORK)
21  @param ds= The target dataset. Leave blank (default) for all datasets.
22  @param outds the output dataset
23 
24  <h4> Dependencies </h4>
25 
26  @version 9.2
27  @author Allan Bowe
28 
29 **/
30 
31 %macro mp_getconstraints(lib=WORK
32  ,ds=
33  ,outds=mp_getconstraints
34 )/*/STORE SOURCE*/;
35 
36 %let lib=%upcase(&lib);
37 %let ds=%upcase(&ds);
38 
39 /* must use SQL as proc datasets does not support length changes */
40 proc sql noprint;
41 create table &outds as
42  select a.TABLE_CATALOG as libref
43  ,a.TABLE_NAME
44  ,a.constraint_type
45  ,a.constraint_name
46  ,b.column_name
47  from dictionary.TABLE_CONSTRAINTS a
48  left join dictionary.constraint_column_usage b
49  on a.TABLE_CATALOG=b.TABLE_CATALOG
50  and a.TABLE_NAME=b.TABLE_NAME
51  and a.constraint_name=b.constraint_name
52  where a.TABLE_CATALOG="&lib"
53  and b.TABLE_CATALOG="&lib"
54  %if "&ds" ne "" %then %do;
55  and a.TABLE_NAME="&ds"
56  and b.TABLE_NAME="&ds"
57  %end;
58  ;
59 
60 %mend;