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