DatabaseProcApplicationCreatedLinks
sybsystemprocssp_fkeys  14 déc. 14Defects Propagation 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 OR REPLACE 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
DEFECTS
 MTYP 4 Assignment type mismatch key_seq: smallint = int 369
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 399
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 405
 QCSC 4 Costly 'select count()', use 'exists()' 138
 QCSC 4 Costly 'select count()', use 'exists()' 203
 QCSC 4 Costly 'select count()', use 'exists()' 246
 QCSC 4 Costly 'select count()', use 'exists()' 247
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {uid}
175
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {uid}
241
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysreferences.csysreferences clustered
(tableid, frgndbname)
Intersection: {frgndbname}
288
 TNOU 4 Table with no unique index sybsystemprocs..sysreferences sybsystemprocs..sysreferences
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 128
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 141
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 150
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 166
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 173
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 193
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 206
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 215
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 232
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 239
 MGTP 3 Grant to public sybsystemprocs..sp_fkeys  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MGTP 3 Grant to public sybsystemprocs..sysreferences  
 MNER 3 No Error Check should check return value of exec 72
 MNER 3 No Error Check should check return value of exec 94
 MNER 3 No Error Check should check @@error after insert 127
 MNER 3 No Error Check should check @@error after insert 140
 MNER 3 No Error Check should check @@error after insert 149
 MNER 3 No Error Check should check @@error after insert 165
 MNER 3 No Error Check should check @@error after insert 172
 MNER 3 No Error Check should check @@error after insert 192
 MNER 3 No Error Check should check @@error after insert 205
 MNER 3 No Error Check should check @@error after insert 214
 MNER 3 No Error Check should check @@error after insert 231
 MNER 3 No Error Check should check @@error after insert 238
 MNER 3 No Error Check should check @@error after delete 312
 MNER 3 No Error Check should check @@error after insert 313
 MNER 3 No Error Check should check @@error after insert 314
 MNER 3 No Error Check should check @@error after insert 315
 MNER 3 No Error Check should check @@error after insert 316
 MNER 3 No Error Check should check @@error after insert 317
 MNER 3 No Error Check should check @@error after insert 318
 MNER 3 No Error Check should check @@error after insert 319
 MNER 3 No Error Check should check @@error after insert 320
 MNER 3 No Error Check should check @@error after insert 321
 MNER 3 No Error Check should check @@error after insert 322
 MNER 3 No Error Check should check @@error after insert 323
 MNER 3 No Error Check should check @@error after insert 324
 MNER 3 No Error Check should check @@error after insert 325
 MNER 3 No Error Check should check @@error after insert 326
 MNER 3 No Error Check should check @@error after insert 327
 MNER 3 No Error Check should check @@error after insert 328
 MNER 3 No Error Check should check @@error after delete 335
 MNER 3 No Error Check should check @@error after insert 336
 MNER 3 No Error Check should check @@error after insert 337
 MNER 3 No Error Check should check @@error after insert 338
 MNER 3 No Error Check should check @@error after insert 339
 MNER 3 No Error Check should check @@error after insert 340
 MNER 3 No Error Check should check @@error after insert 341
 MNER 3 No Error Check should check @@error after insert 342
 MNER 3 No Error Check should check @@error after insert 343
 MNER 3 No Error Check should check @@error after insert 344
 MNER 3 No Error Check should check @@error after insert 345
 MNER 3 No Error Check should check @@error after insert 346
 MNER 3 No Error Check should check @@error after insert 347
 MNER 3 No Error Check should check @@error after insert 348
 MNER 3 No Error Check should check @@error after insert 349
 MNER 3 No Error Check should check @@error after insert 350
 MNER 3 No Error Check should check @@error after insert 351
 MNER 3 No Error Check should check @@error after insert 361
 MNER 3 No Error Check should check return value of exec 399
 MNER 3 No Error Check should check return value of exec 405
 MUCO 3 Useless Code Useless Brackets 39
 MUCO 3 Useless Code Useless Brackets 48
 MUCO 3 Useless Code Useless Brackets 61
 MUCO 3 Useless Code Useless Brackets 74
 MUCO 3 Useless Code Useless Brackets 96
 MUCO 3 Useless Code Useless Brackets 120
 MUCO 3 Useless Code Useless Brackets 138
 MUCO 3 Useless Code Useless Brackets 159
 MUCO 3 Useless Code Useless Brackets 185
 MUCO 3 Useless Code Useless Brackets 203
 MUCO 3 Useless Code Useless Brackets 224
 MUCO 3 Useless Code Useless Brackets 246
 MUCO 3 Useless Code Useless Brackets 251
 MUCO 3 Useless Code Useless Brackets 300
 MUCO 3 Useless Code Useless Brackets 359
 MUIN 3 Column created using implicit nullability 109
 MUIN 3 Column created using implicit nullability 110
 MUIN 3 Column created using implicit nullability 254
 MUIN 3 Column created using implicit nullability 265
 MUIN 3 Column created using implicit nullability 266
 QISO 3 Set isolation level 51
 QJWT 3 Join or Sarg Without Index on temp table 284
 QJWT 3 Join or Sarg Without Index on temp table 286
 VUNU 3 Variable is not used @status 31
 VUNU 3 Variable is not used @msg 32
 CRDO 2 Read Only Cursor Marker (has for read only clause) 277
 MSUB 2 Subquery Marker 363
 MSUB 2 Subquery Marker 365
 MSUB 2 Subquery Marker 367
 MSUB 2 Subquery Marker 369
 MTR1 2 Metrics: Comments Ratio Comments: 25% 16
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 20 = 24dec - 6exi + 2 16
 MTR3 2 Metrics: Query Complexity Complexity: 232 16

DEPENDENCIES
PROCS AND TABLES USED
writes table tempdb..#fkey_res (1) 
reads table sybsystemprocs..sysobjects  
read_writes table tempdb..#fid (1) 
read_writes table tempdb..#pid (1) 
read_writes table tempdb..#fkeys (1) 
calls proc sybsystemprocs..sp_autoformat  
   writes table sybsystemprocs..sp_autoformat_rset_002 
   reads table master..systypes (1)  
   writes table sybsystemprocs..sp_autoformat_rset_001 
   writes table sybsystemprocs..sp_autoformat_rset_004 
   reads table tempdb..syscolumns (1)  
   calls proc sybsystemprocs..sp_autoformat  
   reads table master..syscolumns (1)  
   reads table tempdb..systypes (1)  
   read_writes table tempdb..#colinfo_af (1) 
   writes table sybsystemprocs..sp_autoformat_rset_003 
   calls proc sybsystemprocs..sp_namecrack  
   writes table sybsystemprocs..sp_autoformat_rset_005 
read_writes table tempdb..#pkeys (1) 
reads table sybsystemprocs..sysreferences  
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)