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