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