DatabaseProcApplicationCreatedLinks
sybsystemprocssp_jdbc_fkeys  31 Aug 14Defects Dependencies

1     /** SECTION END: CLEANUP **/
2     
3     /*
4     ** parameters: @pktable_name - table name for primary key
5     **             @pktable_owner - (schema) a schema name pattern; "" retrieves 
6     **		those without a schema
7     **             @pktable_qualifier - (catalog name) a catalog name; "" retrieves
8     **              those without a catalog; null means drop catalog name from the
9     **                  selection criteria 
10    **             @fktable_name - table name for foreign key
11    **             @fktable_owner - (schema) a schema name pattern; "" retrieves 
12    **		those  without a schema
13    **             @fktable_qualifier - (catalog name) a catalog name; "" retrieves
14    **              those without a catalog; null means drop catalog name from the
15    **              selection criteria 
16    **
17    ** note: there is one raiserror message: 18040
18    **
19    ** messages for 'sp_jdbc_fkeys'               18039, 18040
20    **
21    ** 17461, 'Object does not exist in this database.'
22    ** 18040, 'Catalog procedure %1! can not be run in a transaction.', sp_jdbc_fkeys
23    ** 18043 ' Primary key table name or foreign key table name or both must be
24    ** given'
25    ** 18044, '%1! table qualifier must be name of current database.' [Primary
26    ** key | Foreign key]
27    **
28    */
29    
30    CREATE PROCEDURE sp_jdbc_fkeys
31        @pktable_name varchar(300) = null,
32        @pktable_owner varchar(32) = null,
33        @pktable_qualifier varchar(32) = null,
34        @fktable_name varchar(300) = null,
35        @fktable_owner varchar(32) = null,
36        @fktable_qualifier varchar(32) = null
37    AS
38        /* Don't delete the following line. It is the checkpoint for sed */
39        /* Server dependent stored procedure add here ad ADDPOINT_FKEYS */
40        declare @ftabid int, @ptabid int, @constrid int, @keycnt int, @primkey int
41        declare @fokey1 int, @fokey2 int, @fokey3 int, @fokey4 int, @fokey5 int
42        declare @fokey6 int, @fokey7 int, @fokey8 int, @fokey9 int, @fokey10 int
43        declare @fokey11 int, @fokey12 int, @fokey13 int, @fokey14 int, @fokey15 int
44        declare @refkey1 int, @refkey2 int, @refkey3 int, @refkey4 int, @refkey5 int
45        declare @refkey6 int, @refkey7 int, @refkey8 int, @refkey9 int, @refkey10 int
46        declare @refkey11 int, @refkey12 int, @refkey13 int, @refkey14 int
47        declare @refkey15 int, @refkey16 int, @fokey16 int, @status int, @i int
48        declare @msg varchar(255)
49        declare @msg2 varchar(50)
50        declare @export int, @import int
51        declare @notDeferrable int
52        declare @startedInTransaction bit
53    
54        if (@@trancount = 0)
55        begin
56            set chained off
57        end
58    
59        /* check if we're in a transaction before we execute any selects */
60        if (@@trancount > 0)
61            select @startedInTransaction = 1
62        else
63            select @startedInTransaction = 0
64    
65        /* this will make sure that all rows are sent even if
66        ** the client "set rowcount" is differect
67        */
68    
69        set rowcount 0
70    
71    
72        select @notDeferrable = 7
73        select @import = 0
74        select @export = 0
75    
76        /* if table_owner is null, include all in search */
77        if (@fktable_owner is null) select @fktable_owner = '%'
78        if (@pktable_owner is null) select @pktable_owner = '%'
79    
80        set nocount on
81    
82        set transaction isolation level 1
83    
84        if (@startedInTransaction = 1)
85            save transaction jdbc_keep_temptables_from_tx
86    
87        if (@pktable_name is null) and (@fktable_name is null)
88        begin
89            /* If neither primary key nor foreign key table names given */
90            /*
91            ** 18043 'Primary key table name or foreign key table name
92            ** or both must be given'
93            */
94            exec sp_getmessage 18043, @msg output
95            raiserror 18043 @msg
96            return (1)
97        end
98        else
99        begin
100           if (substring(@pktable_name, 1, 1) = '#') or
101               (substring(@fktable_name, 1, 1) = '#')
102           begin
103               /* We won't allow temptables here
104               ** 
105               ** Error 177: cannot create a temporary object (with
106               ** '#' as the first character name.
107               */
108               exec sp_getmessage 17676, @msg out
109               raiserror 17676 @msg
110               return (1)
111           end
112       end
113       if @fktable_qualifier is not null
114       begin
115           if db_name() != @fktable_qualifier
116           begin
117               exec sp_getmessage 18039, @msg out
118               raiserror 18039 @msg
119               return (1)
120           end
121       end
122       else
123       begin
124           /*
125           ** Now make sure that foreign table qualifier is pointing to the
126           ** current database in case it is not specified.
127           */
128           select @fktable_qualifier = db_name()
129       end
130   
131       if @pktable_qualifier is not null
132       begin
133           if db_name() != @pktable_qualifier
134           begin
135               exec sp_getmessage 18039, @msg output
136               raiserror 18039 @msg
137               return (1)
138           end
139       end
140       else
141       begin
142           /*
143           ** Now make sure that primary table qualifier is pointing to the
144           ** current database in case it is not specified.
145           */
146           select @pktable_qualifier = db_name()
147       end
148   
149       create table #jpid(pid int, uid int, name varchar(32))
150       create table #jfid(fid int, uid int, name varchar(32))
151   
152       if @pktable_name is not null
153       begin
154           select @export = 1
155           if ((select count(*) from sysobjects
156                       where name = @pktable_name
157                           and user_name(uid) like @pktable_owner ESCAPE '\'
158                           and type in ('S', 'U')) = 0)
159           begin
160               exec sp_getmessage 17674, @msg output
161               raiserror 17674 @msg
162               return (1)
163           end
164   
165           insert into #jpid
166           select id, uid, name
167           from sysobjects
168           where name = @pktable_name
169               and user_name(uid) like @pktable_owner ESCAPE '\'
170               and type in ('S', 'U')
171       end
172       else
173       begin
174           insert into #jpid
175           select id, uid, name
176           from sysobjects
177           where type in ('S', 'U')
178               and user_name(uid) like @pktable_owner ESCAPE '\'
179       end
180   
181       if @fktable_name is not null
182       begin
183           select @import = 1
184           if ((select count(*)
185                       from sysobjects
186                       where name = @fktable_name
187                           and type in ('S', 'U')
188                           and user_name(uid) like @fktable_owner ESCAPE '\') = 0)
189           begin
190               exec sp_getmessage 17674, @msg output
191               raiserror 17674 @msg
192               return (1)
193           end
194           insert into #jfid
195           select id, uid, name
196           from sysobjects
197           where name = @fktable_name
198               and type in ('S', 'U')
199               and user_name(uid) like @fktable_owner ESCAPE '\'
200       end
201       else
202       begin
203           insert into #jfid
204           select id, uid, name
205           from sysobjects where
206               type in ('S', 'U')
207               and user_name(uid) like @fktable_owner ESCAPE '\'
208       end
209   
210       create table #jfkey_res(
211           PKTABLE_CAT varchar(32) null,
212           PKTABLE_SCHEM varchar(32) null,
213           PKTABLE_NAME varchar(257) null,
214           PKCOLUMN_NAME varchar(257) null,
215           FKTABLE_CAT varchar(32) null,
216           FKTABLE_SCHEM varchar(32) null,
217           FKTABLE_NAME varchar(257) null,
218           FKCOLUMN_NAME varchar(257) null,
219           KEY_SEQ smallint,
220           UPDATE_RULE smallint,
221           DELETE_RULE smallint,
222           FK_NAME varchar(257),
223           PK_NAME varchar(257) null)
224       create table #jpkeys(seq int, keys varchar(32) null)
225       create table #jfkeys(seq int, keys varchar(32) null)
226   
227       /*
228       ** Since there are possibly multiple rows in sysreferences
229       ** that describe foreign and primary key relationships among
230       ** two tables, so we declare a cursor on the selection from
231       ** sysreferences and process the output at row by row basis.
232       */
233   
234       declare jcurs_sysreferences cursor
235       for
236       select tableid, reftabid, constrid, keycnt,
237           fokey1, fokey2, fokey3, fokey4, fokey5, fokey6, fokey7, fokey8,
238           fokey9, fokey10, fokey11, fokey12, fokey13, fokey14, fokey15,
239           fokey16, refkey1, refkey2, refkey3, refkey4, refkey5,
240           refkey6, refkey7, refkey8, refkey9, refkey10, refkey11,
241           refkey12, refkey13, refkey14, refkey15, refkey16
242       from sysreferences
243       where tableid in (
244               select fid from #jfid)
245           and reftabid in (
246               select pid from #jpid)
247           and frgndbname is NULL and pmrydbname is NULL
248       for read only
249   
250       open jcurs_sysreferences
251   
252       fetch jcurs_sysreferences into @ftabid, @ptabid, @constrid, @keycnt,
253           @fokey1, @fokey2, @fokey3, @fokey4, @fokey5, @fokey6, @fokey7, @fokey8,
254           @fokey9, @fokey10, @fokey11, @fokey12, @fokey13, @fokey14, @fokey15,
255           @fokey16, @refkey1, @refkey2, @refkey3, @refkey4, @refkey5, @refkey6,
256           @refkey7, @refkey8, @refkey9, @refkey10, @refkey11, @refkey12,
257           @refkey13, @refkey14, @refkey15, @refkey16
258   
259       while (@@sqlstatus = 0)
260       begin
261           /*
262           ** For each row of sysreferences which describes a foreign-
263           ** primary key relationship, do the following.
264           */
265   
266           /*
267           ** First store the column names that belong to primary keys
268           ** in table #pkeys for later retrieval.
269           */
270   
271           delete #jpkeys
272           insert #jpkeys values (1, col_name(@ptabid, @refkey1))
273           insert #jpkeys values (2, col_name(@ptabid, @refkey2))
274           insert #jpkeys values (3, col_name(@ptabid, @refkey3))
275           insert #jpkeys values (4, col_name(@ptabid, @refkey4))
276           insert #jpkeys values (5, col_name(@ptabid, @refkey5))
277           insert #jpkeys values (6, col_name(@ptabid, @refkey6))
278           insert #jpkeys values (7, col_name(@ptabid, @refkey7))
279           insert #jpkeys values (8, col_name(@ptabid, @refkey8))
280           insert #jpkeys values (9, col_name(@ptabid, @refkey9))
281           insert #jpkeys values (10, col_name(@ptabid, @refkey10))
282           insert #jpkeys values (11, col_name(@ptabid, @refkey11))
283           insert #jpkeys values (12, col_name(@ptabid, @refkey12))
284           insert #jpkeys values (13, col_name(@ptabid, @refkey13))
285           insert #jpkeys values (14, col_name(@ptabid, @refkey14))
286           insert #jpkeys values (15, col_name(@ptabid, @refkey15))
287           insert #jpkeys values (16, col_name(@ptabid, @refkey16))
288   
289           /*
290           ** Second store the column names that belong to foreign keys
291           ** in table #jfkeys for later retrieval.
292           */
293   
294           delete #jfkeys
295           insert #jfkeys values (1, col_name(@ftabid, @fokey1))
296           insert #jfkeys values (2, col_name(@ftabid, @fokey2))
297           insert #jfkeys values (3, col_name(@ftabid, @fokey3))
298           insert #jfkeys values (4, col_name(@ftabid, @fokey4))
299           insert #jfkeys values (5, col_name(@ftabid, @fokey5))
300           insert #jfkeys values (6, col_name(@ftabid, @fokey6))
301           insert #jfkeys values (7, col_name(@ftabid, @fokey7))
302           insert #jfkeys values (8, col_name(@ftabid, @fokey8))
303           insert #jfkeys values (9, col_name(@ftabid, @fokey9))
304           insert #jfkeys values (10, col_name(@ftabid, @fokey10))
305           insert #jfkeys values (11, col_name(@ftabid, @fokey11))
306           insert #jfkeys values (12, col_name(@ftabid, @fokey12))
307           insert #jfkeys values (13, col_name(@ftabid, @fokey13))
308           insert #jfkeys values (14, col_name(@ftabid, @fokey14))
309           insert #jfkeys values (15, col_name(@ftabid, @fokey15))
310           insert #jfkeys values (16, col_name(@ftabid, @fokey16))
311   
312           /*
313           ** For each column of the current foreign-primary key relation,
314           ** create a row into result table: #jfkey_res.
315           */
316   
317           select @i = 1
318           while (@i <= @keycnt)
319           begin
320               insert into #jfkey_res
321               select @pktable_qualifier,
322                       (select user_name(uid) from #jpid where
323                           pid = @ptabid),
324                   object_name(@ptabid),
325                       (select keys from #jpkeys where seq = @i),
326                   @fktable_qualifier,
327                       (select user_name(uid) from #jfid where
328                           fid = @ftabid),
329                   object_name(@ftabid),
330                       (select keys from #jfkeys where seq = @i),
331                   @i, 1, 1,
332                   /*Foreign key name*/
333                   object_name(@constrid),
334                       /* Primary key name */
335                       (select name from sysindexes where id = @ftabid
336                           and status > 2048 and status < 32768)
337               select @i = @i + 1
338           end
339   
340           /*
341           ** Go to the next foreign-primary key relationship if any.
342           */
343   
344           fetch jcurs_sysreferences into @ftabid, @ptabid, @constrid,
345               @keycnt, @fokey1, @fokey2, @fokey3, @fokey4, @fokey5, @fokey6,
346               @fokey7, @fokey8, @fokey9, @fokey10, @fokey11, @fokey12,
347               @fokey13, @fokey14, @fokey15, @fokey16, @refkey1, @refkey2,
348               @refkey3, @refkey4, @refkey5, @refkey6, @refkey7, @refkey8,
349               @refkey9, @refkey10, @refkey11, @refkey12, @refkey13, @refkey14,
350               @refkey15, @refkey16
351       end
352   
353       close jcurs_sysreferences
354       deallocate cursor jcurs_sysreferences
355   
356       /*
357       ** Everything is now in the result table #jfkey_res, so go ahead
358       ** and select from the table now.
359       */
360       if (@export = 1) and (@import = 0)
361       begin
362           select PKTABLE_CAT,
363               PKTABLE_SCHEM,
364               PKTABLE_NAME,
365               PKCOLUMN_NAME,
366               FKTABLE_CAT,
367               FKTABLE_SCHEM,
368               FKTABLE_NAME,
369               FKCOLUMN_NAME,
370               KEY_SEQ,
371               UPDATE_RULE,
372               DELETE_RULE,
373               FK_NAME,
374               PK_NAME,
375               @notDeferrable as DEFERRABILITY
376           from #jfkey_res
377           where PKTABLE_SCHEM like @pktable_owner ESCAPE '\'
378           order by FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, KEY_SEQ
379       end
380   
381       if (@export = 0) and (@import = 1)
382       begin
383           select PKTABLE_CAT,
384               PKTABLE_SCHEM,
385               PKTABLE_NAME,
386               PKCOLUMN_NAME,
387               FKTABLE_CAT,
388               FKTABLE_SCHEM,
389               FKTABLE_NAME,
390               FKCOLUMN_NAME,
391               KEY_SEQ,
392               UPDATE_RULE,
393               DELETE_RULE,
394               FK_NAME,
395               PK_NAME,
396               @notDeferrable as DEFERRABILITY
397           from #jfkey_res
398           where FKTABLE_SCHEM like @fktable_owner ESCAPE '\'
399           order by PKTABLE_CAT, PKTABLE_SCHEM, PKTABLE_NAME, KEY_SEQ
400       end
401   
402       if (@export = 1) and (@import = 1)
403       begin
404           select PKTABLE_CAT,
405               PKTABLE_SCHEM,
406               PKTABLE_NAME,
407               PKCOLUMN_NAME,
408               FKTABLE_CAT,
409               FKTABLE_SCHEM,
410               FKTABLE_NAME,
411               FKCOLUMN_NAME,
412               KEY_SEQ,
413               UPDATE_RULE,
414               DELETE_RULE,
415               FK_NAME,
416               PK_NAME,
417               @notDeferrable as DEFERRABILITY
418           from #jfkey_res
419           where PKTABLE_SCHEM like @pktable_owner ESCAPE '\'
420               and FKTABLE_SCHEM like @fktable_owner ESCAPE '\'
421           order by FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, KEY_SEQ
422       end
423   
424       if (@startedInTransaction = 1)
425           rollback transaction jdbc_keep_temptables_from_tx
426   
427   
428   


exec sp_procxmode 'sp_jdbc_fkeys', 'AnyMode'
go
RESULT SETS
sp_jdbc_fkeys_rset_001
sp_jdbc_fkeys_rset_003
sp_jdbc_fkeys_rset_002

DEFECTS
 MTYP 4 Assignment type mismatch name: varchar(32) = longsysname(255) 166
 MTYP 4 Assignment type mismatch name: varchar(32) = longsysname(255) 175
 MTYP 4 Assignment type mismatch name: varchar(32) = longsysname(255) 195
 MTYP 4 Assignment type mismatch name: varchar(32) = longsysname(255) 204
 MTYP 4 Assignment type mismatch KEY_SEQ: smallint = int 331
 QCSC 4 Costly 'select count()', use 'exists()' 155
 QCSC 4 Costly 'select count()', use 'exists()' 184
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {uid}
177
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {uid}
206
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysreferences.csysreferences clustered
(tableid, frgndbname)
Intersection: {frgndbname}
247
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 336
 TNOU 4 Table with no unique index sybsystemprocs..sysreferences sybsystemprocs..sysreferences
 MGTP 3 Grant to public sybsystemprocs..sysindexes  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MGTP 3 Grant to public sybsystemprocs..sysreferences  
 MNER 3 No Error Check should check return value of exec 94
 MNER 3 No Error Check should check return value of exec 108
 MNER 3 No Error Check should check return value of exec 117
 MNER 3 No Error Check should check return value of exec 135
 MNER 3 No Error Check should check return value of exec 160
 MNER 3 No Error Check should check @@error after insert 165
 MNER 3 No Error Check should check @@error after insert 174
 MNER 3 No Error Check should check return value of exec 190
 MNER 3 No Error Check should check @@error after insert 194
 MNER 3 No Error Check should check @@error after insert 203
 MNER 3 No Error Check should check @@error after delete 271
 MNER 3 No Error Check should check @@error after insert 272
 MNER 3 No Error Check should check @@error after insert 273
 MNER 3 No Error Check should check @@error after insert 274
 MNER 3 No Error Check should check @@error after insert 275
 MNER 3 No Error Check should check @@error after insert 276
 MNER 3 No Error Check should check @@error after insert 277
 MNER 3 No Error Check should check @@error after insert 278
 MNER 3 No Error Check should check @@error after insert 279
 MNER 3 No Error Check should check @@error after insert 280
 MNER 3 No Error Check should check @@error after insert 281
 MNER 3 No Error Check should check @@error after insert 282
 MNER 3 No Error Check should check @@error after insert 283
 MNER 3 No Error Check should check @@error after insert 284
 MNER 3 No Error Check should check @@error after insert 285
 MNER 3 No Error Check should check @@error after insert 286
 MNER 3 No Error Check should check @@error after insert 287
 MNER 3 No Error Check should check @@error after delete 294
 MNER 3 No Error Check should check @@error after insert 295
 MNER 3 No Error Check should check @@error after insert 296
 MNER 3 No Error Check should check @@error after insert 297
 MNER 3 No Error Check should check @@error after insert 298
 MNER 3 No Error Check should check @@error after insert 299
 MNER 3 No Error Check should check @@error after insert 300
 MNER 3 No Error Check should check @@error after insert 301
 MNER 3 No Error Check should check @@error after insert 302
 MNER 3 No Error Check should check @@error after insert 303
 MNER 3 No Error Check should check @@error after insert 304
 MNER 3 No Error Check should check @@error after insert 305
 MNER 3 No Error Check should check @@error after insert 306
 MNER 3 No Error Check should check @@error after insert 307
 MNER 3 No Error Check should check @@error after insert 308
 MNER 3 No Error Check should check @@error after insert 309
 MNER 3 No Error Check should check @@error after insert 310
 MNER 3 No Error Check should check @@error after insert 320
 MUCO 3 Useless Code Useless Brackets 54
 MUCO 3 Useless Code Useless Brackets 60
 MUCO 3 Useless Code Useless Brackets 77
 MUCO 3 Useless Code Useless Brackets 78
 MUCO 3 Useless Code Useless Brackets 84
 MUCO 3 Useless Code Useless Brackets 96
 MUCO 3 Useless Code Useless Brackets 110
 MUCO 3 Useless Code Useless Brackets 119
 MUCO 3 Useless Code Useless Brackets 137
 MUCO 3 Useless Code Useless Brackets 155
 MUCO 3 Useless Code Useless Brackets 162
 MUCO 3 Useless Code Useless Brackets 184
 MUCO 3 Useless Code Useless Brackets 192
 MUCO 3 Useless Code Useless Brackets 259
 MUCO 3 Useless Code Useless Brackets 318
 MUCO 3 Useless Code Useless Brackets 424
 MUIN 3 Column created using implicit nullability 149
 MUIN 3 Column created using implicit nullability 150
 MUIN 3 Column created using implicit nullability 210
 MUIN 3 Column created using implicit nullability 224
 MUIN 3 Column created using implicit nullability 225
 QCRS 3 Conditional Result Set 362
 QCRS 3 Conditional Result Set 383
 QCRS 3 Conditional Result Set 404
 QISO 3 Set isolation level 82
 QJWT 3 Join or Sarg Without Index on temp table 243
 QJWT 3 Join or Sarg Without Index on temp table 245
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysindexes.csysindexes unique clustered
(id, indid)
Intersection: {id}
335
 VUNU 3 Variable is not used @primkey 40
 VUNU 3 Variable is not used @status 47
 VUNU 3 Variable is not used @msg2 49
 CRDO 2 Read Only Cursor Marker (has for read only clause) 236
 MRST 2 Result Set Marker 362
 MRST 2 Result Set Marker 383
 MRST 2 Result Set Marker 404
 MSUB 2 Subquery Marker 155
 MSUB 2 Subquery Marker 184
 MSUB 2 Subquery Marker 322
 MSUB 2 Subquery Marker 325
 MSUB 2 Subquery Marker 327
 MSUB 2 Subquery Marker 330
 MSUB 2 Subquery Marker 335
 MTR1 2 Metrics: Comments Ratio Comments: 20% 30
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 28 = 33dec - 7exi + 2 30
 MTR3 2 Metrics: Query Complexity Complexity: 243 30

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..sysobjects  
reads table sybsystemprocs..sysindexes  
read_writes table tempdb..#jfkeys (1) 
reads table sybsystemprocs..sysreferences  
read_writes table tempdb..#jfid (1) 
read_writes table tempdb..#jfkey_res (1) 
read_writes table tempdb..#jpid (1) 
read_writes table tempdb..#jpkeys (1) 
calls proc sybsystemprocs..sp_getmessage  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..syslanguages (1)  
   reads table sybsystemprocs..sysusermessages  
   reads table master..sysmessages (1)  

CALLERS
called by proc sybsystemprocs..sp_jdbc_importkey  
called by proc sybsystemprocs..sp_jdbc_exportkey  
called by proc sybsystemprocs..sp_jdbc_getcrossreferences