mp_getpk.sas
Go to the documentation of this file.
1 /**
2  @file
3  @brief Extract the primary key fields from a table or library
4  @details Examines the constraints to identify primary key fields - indicated
5  by an explicit PK constraint, or a unique index that is also NOT NULL.
6 
7  Can be executed at both table and library level. Supports both BASE engine
8  libraries and SQL Server.
9 
10  Usage:
11 
12  proc sql;
13  create table work.example(
14  TX_FROM float format=datetime19.,
15  DD_TYPE char(16),
16  DD_SOURCE char(2048),
17  DD_SHORTDESC char(256),
18  constraint pk primary key(tx_from, dd_type,dd_source),
19  constraint unq unique(tx_from, dd_type),
20  constraint nnn not null(DD_SHORTDESC)
21  );
22  %mp_getpk(work,ds=example)
23 
24  Returns:
25 
26 |libref:$8.|dsn:$32.|memtype:$8.|dbms_memtype:$32.|typemem:$8.|memlabel:$256.|nvar:best.|compress:$8.|pk_fields:$512.|
27 |---|---|---|---|---|---|---|---|---|
28 |WORK|EXAMPLE|DATA| |DATA| |4|NO|TX_FROM DD_TYPE DD_SOURCE|
29 
30 
31  @param [in] lib The libref to examine
32  @param [in] ds= (0) Select the dataset to examine, else use 0 for all tables
33  @param [in] mdebug= (0) Set to 1 to preserve temp tables, print var values etc
34  @param [out] outds= (work.mp_getpk) The name of the output table to create.
35 
36  <h4> SAS Macros </h4>
37  @li mf_existfeature.sas
38  @li mf_getengine.sas
39  @li mf_getschema.sas
40  @li mp_dropmembers.sas
41  @li mp_getconstraints.sas
42 
43  <h4> Related Macros </h4>
44  @li mp_getpk.test.sas
45  @li mp_guesspk.sas
46 
47  @version 9.3
48  @author Macro People Ltd
49 **/
50 
51 %macro mp_getpk(
52  lib,
53  ds=0,
54  outds=work.mp_getpk,
55  mdebug=0
56 )/*/STORE SOURCE*/;
57 
58 
59 %local engine schema ds1 ds2 ds3 dsn tabs1 tabs2 sum pk4sure pkdefault finalpks
60  pkfromindex;
61 
62 %let lib=%upcase(&lib);
63 %let ds=%upcase(&ds);
64 %let engine=%mf_getengine(&lib);
65 %let schema=%mf_getschema(&lib);
66 
67 %let ds1=%mf_getuniquename(prefix=getpk_ds1);
68 %let ds2=%mf_getuniquename(prefix=getpk_ds2);
69 %let ds3=%mf_getuniquename(prefix=getpk_ds3);
70 %let tabs1=%mf_getuniquename(prefix=getpk_tabs1);
71 %let tabs2=%mf_getuniquename(prefix=getpk_tabs2);
72 %let sum=%mf_getuniquename(prefix=getpk_sum);
73 %let pk4sure=%mf_getuniquename(prefix=getpk_pk4sure);
74 %let pkdefault=%mf_getuniquename(prefix=getpk_pkdefault);
75 %let pkfromindex=%mf_getuniquename(prefix=getpk_pkfromindex);
76 %let finalpks=%mf_getuniquename(prefix=getpk_finalpks);
77 
78 %local dbg;
79 %if &mdebug=1 %then %do;
80  %put &sysmacroname entry vars:;
81  %put _local_;
82 %end;
83 %else %let dbg=*;
84 
85 proc sql;
86 create table &ds1 as
87  select libname as libref
88  ,upcase(memname) as dsn
89  ,memtype
90  ,upcase(name) as name
91  ,type
92  ,length
93  ,varnum
94  ,label
95  ,format
96  ,idxusage
97  ,notnull
98  from dictionary.columns
99  where upcase(libname)="&lib"
100 %if &ds ne 0 %then %do;
101  and upcase(memname)="&ds"
102 %end;
103  ;
104 
105 
106 %if &engine=SQLSVR %then %do;
107  proc sql;
108  connect using &lib;
109  create table work.&ds2 as
110  select * from connection to &lib(
111  select
112  s.name as SchemaName,
113  t.name as memname,
114  tc.name as name,
115  ic.key_ordinal as KeyOrderNr
116  from
117  sys.schemas s
118  inner join sys.tables t on s.schema_id=t.schema_id
119  inner join sys.indexes i on t.object_id=i.object_id
120  inner join sys.index_columns ic on i.object_id=ic.object_id
121  and i.index_id=ic.index_id
122  inner join sys.columns tc on ic.object_id=tc.object_id
123  and ic.column_id=tc.column_id
124  where i.is_primary_key=1
125  and s.name=%str(%')&schema%str(%')
126  order by t.name, ic.key_ordinal ;
127  );disconnect from &lib;
128  create table &ds3 as
129  select a.*
130  ,case when b.name is not null then 1 else 0 end as pk_ind
131  from work.&ds1 a
132  left join work.&ds2 b
133  on a.dsn=b.memname
134  and upcase(a.name)=upcase(b.name)
135  order by libref,dsn;
136 %end;
137 %else %do;
138 
139  %if &ds = 0 %then %let dsn=;
140 
141  /* get all constraints, in constraint order*/
142  %mp_getconstraints(lib=&lib,ds=&dsn,outds=work.&ds2)
143 
144  /* extract cols that are clearly primary keys */
145  proc sql;
146  create table &pk4sure as
147  select libref
148  ,table_name
149  ,constraint_name
150  ,constraint_order
151  ,column_name as name
152  from work.&ds2
153  where constraint_type='PRIMARY'
154  order by 1,2,3,4;
155 
156  /* extract unique constraints where every col is also NOT NULL */
157  proc sql;
158  create table &sum as
159  select a.libref
160  ,a.table_name
161  ,a.constraint_name
162  ,count(a.column_name) as unq_cnt
163  ,count(b.column_name) as nul_cnt
164  from work.&ds2(where=(constraint_type ='UNIQUE')) a
165  left join work.&ds2(where=(constraint_type ='NOT NULL')) b
166  on a.libref=b.libref
167  and a.table_name=b.table_name
168  and a.column_name=b.column_name
169  group by 1,2,3
170  having unq_cnt=nul_cnt;
171 
172  /* extract cols from the relevant unique constraints */
173  create table &pkdefault as
174  select a.libref
175  ,a.table_name
176  ,a.constraint_name
177  ,b.constraint_order
178  ,b.column_name as name
179  from &sum a
180  left join &ds2(where=(constraint_type ='UNIQUE')) b
181  on a.libref=b.libref
182  and a.table_name=b.table_name
183  and a.constraint_name=b.constraint_name
184  order by 1,2,3,4;
185 
186  /* extract cols from the relevant unique INDEXES */
187  create table &pkfromindex as
188  select libname as libref
189  ,memname as table_name
190  ,indxname as constraint_name
191  ,indxpos as constraint_order
192  ,name
193  from dictionary.indexes
194  where nomiss='yes' and unique='yes' and upcase(libname)="&lib"
195  %if &ds ne 0 %then %do;
196  and upcase(memname)="&ds"
197  %end;
198  order by 1,2,3,4;
199 
200  /* create one table */
201  data &finalpks;
202  set &pkdefault &pk4sure &pkfromindex;
203  pk_ind=1;
204  /* if there are multiple unique constraints, take the first */
205  by libref table_name constraint_name;
206  retain keepme;
207  if first.table_name then keepme=1;
208  if first.constraint_name and not first.table_name then keepme=0;
209  if keepme=1;
210  run;
211 
212  /* join back to starting table */
213  proc sql;
214  create table &ds3 as
215  select a.*
216  ,b.constraint_order
217  ,case when b.pk_ind=1 then 1 else 0 end as pk_ind
218  from work.&ds1 a
219  left join work.&finalpks b
220  on a.libref=b.libref
221  and a.dsn=b.table_name
222  and upcase(a.name)=upcase(b.name)
223  order by libref,dsn,constraint_order;
224 %end;
225 
226 
227 /* prepare tables */
228 proc sql;
229 create table work.&tabs1 as select
230  libname as libref
231  ,upcase(memname) as dsn
232  ,memtype
233 %if %mf_existfeature(DBMS_MEMTYPE)=1 %then %do;
234  ,dbms_memtype
235 %end;
236 %else %do;
237  ,'n/a' as dbms_memtype format=$32.
238 %end;
239  ,typemem
240  ,memlabel
241  ,nvar
242  ,compress
243 from dictionary.tables
244  where upcase(libname)="&lib"
245 %if &ds ne 0 %then %do;
246  and upcase(memname)="&ds"
247 %end;
248  ;
249 data &tabs2;
250  set &ds3;
251  length pk_fields $512;
252  retain pk_fields;
253  by libref dsn constraint_order;
254  if first.dsn then pk_fields='';
255  if pk_ind=1 then pk_fields=catx(' ',pk_fields,name);
256  if last.dsn then output;
257 run;
258 
259 proc sql;
260 create table &outds as
261  select a.libref
262  ,a.dsn
263  ,a.memtype
264  ,a.dbms_memtype
265  ,a.typemem
266  ,a.memlabel
267  ,a.nvar
268  ,a.compress
269  ,b.pk_fields
270  from work.&tabs1 a
271  left join work.&tabs2 b
272  on a.libref=b.libref
273  and a.dsn=b.dsn;
274 
275 /* tidy up */
276 %mp_dropmembers(
277  &ds1 &ds2 &ds3 &dsn &tabs1 &tabs2 &sum &pk4sure &pkdefault &finalpks,
278  iftrue=(&mdebug=0)
279 )
280 
281 %mend mp_getpk;