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