Database | Proc | Application | Created | Links |
sybsystemprocs | sp_jdbc_fkeys ![]() | ![]() | 31 Aug 14 | Defects 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 |
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 ![]() |