DatabaseProcApplicationCreatedLinks
sybsystemprocssp_foreignkey  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
3     /*	4.8	1.1	06/14/90	sproc/src/fixindex */
4     
5     /*
6     ** Messages for "sp_foreignkey"         17560
7     **
8     ** 17390, "Table or view name must be in 'current' database."
9     ** 17560, "Foreign key table doesn't exist."
10    ** 17561, "Primary key table doesn't exist."
11    ** 17562, "Only the owner of the table may define a foreign key."
12    ** 17563, "The table does not have a column named '%1!'."
13    ** 	  ** substitute in "@col[1-8]" for the arg.
14    ** 17564, "Primary key does not exist with the same number of columns as the foreign key."
15    ** 17565, "Primary key does not exist."
16    ** 17566, "Datatypes of the column '%1!' in the keys are different." 
17    ** 	  ** substitute in "@col[1-8]" for the arg.
18    ** 17567, "New foreign key added."
19    ** 17756, "The execution of the stored procedure '%1!' in database
20    **         '%2!' was aborted because there was an error in writing the
21    **         replication log record."
22    */
23    
24    /*
25    ** IMPORTANT NOTE:
26    ** This stored procedure uses the built-in function object_id() in the
27    ** where clause of a select query. If you intend to change this query
28    ** or use the object_id() or db_id() builtin in this procedure, please read the
29    ** READ.ME file in the $DBMS/generic/sproc directory to ensure that the rules
30    ** pertaining to object-id's and db-id's outlined there, are followed.
31    */
32    
33    create or replace procedure sp_foreignkey
34        @tabname varchar(767), /* name of table that we are doing */
35        @pktabname varchar(767), /* name of table with primary key */
36        @col1 varchar(255), /* column name of table we are doing */
37        @col2 varchar(255) = NULL,
38        @col3 varchar(255) = NULL,
39        @col4 varchar(255) = NULL,
40        @col5 varchar(255) = NULL,
41        @col6 varchar(255) = NULL,
42        @col7 varchar(255) = NULL,
43        @col8 varchar(255) = NULL
44    as
45    
46        declare @uid int /* id of owner of the talbe */
47        declare @cnt int /* how many columns are in foreign key */
48    
49        declare @pkey1 int /* colids of the foreign key */
50        declare @pkey2 int
51        declare @pkey3 int
52        declare @pkey4 int
53        declare @pkey5 int
54        declare @pkey6 int
55        declare @pkey7 int
56        declare @pkey8 int
57    
58        declare @fkey1 int /* colids of the primary key */
59        declare @fkey2 int
60        declare @fkey3 int
61        declare @fkey4 int
62        declare @fkey5 int
63        declare @fkey6 int
64        declare @fkey7 int
65        declare @fkey8 int
66    
67        declare @msg varchar(1024)
68        declare @dbname varchar(30)
69    
70    
71        if @@trancount = 0
72        begin
73            set chained off
74        end
75    
76        set transaction isolation level 1
77    
78        /*
79        **  Check to see that the tabname is local.
80        */
81        if @tabname like "%.%.%"
82        begin
83            if substring(@tabname, 1, charindex(".", @tabname) - 1) != db_name()
84            begin
85                /*
86                ** 17390, "Table or view name must be in 'current' database."
87                */
88                raiserror 17390
89                return (1)
90            end
91        end
92        if @pktabname like "%.%.%"
93        begin
94            if substring(@pktabname, 1, charindex(".", @pktabname) - 1) != db_name()
95            begin
96                /*
97                ** 17390, "Table or view name must be in 'current' database."
98                */
99                raiserror 17390
100               return (1)
101           end
102       end
103   
104       /*
105       **  See if we can find the objects.  They must be a system table, user table,
106       **  or view.  The low 3 bits of sysobjects.sysstat indicate what the 
107       **  object type is -- it's more reliable than using sysobjects.type which
108       **  could change.
109       */
110       if not exists (select *
111               from sysobjects
112               where id = object_id(@tabname)
113                   and (sysstat & 7 = 1 /* system table */
114                       or sysstat & 7 = 2 /* view */
115                       or sysstat & 7 = 3)) /* user table */
116       /*
117       **  If either of the tables don't exist, quit.
118       */
119       begin
120           /*
121           ** 17560, "Foreign key table doesn't exist."
122           */
123           raiserror 17560
124           return (1)
125       end
126       if not exists (select *
127               from sysobjects
128               where id = object_id(@pktabname)
129                   and (sysstat & 7 = 1
130                       or sysstat & 7 = 2
131                       or sysstat & 7 = 3))
132       begin
133           /*
134           ** 17561, "Primary key table doesn't exist."
135           */
136           raiserror 17561
137           return (1)
138       end
139   
140       /*
141       **  Only the owner of the table can define it's foreign keys.
142       */
143       select @uid = uid
144       from sysobjects
145       where id = object_id(@tabname)
146           and (sysstat & 7 = 1 /* system table */
147               or sysstat & 7 = 2 /* view */
148               or sysstat & 7 = 3) /* user table */
149   
150       if @uid != user_id()
151       begin
152           /*
153           ** 17562, "Only the owner of the table may define a foreign key."
154           */
155           raiserror 17562
156           return (1)
157       end
158   
159       /*
160       **  Now check to see that the foreign key columns exist.
161       **
162       **  Algorithm is "if key exists
163       **			set @fkey[n] to colid
164       **			incr cnt
165       **			unless there is no colid
166       **			then raise an error
167       **		  cascade to next key
168       */
169       select @cnt = 1, @fkey1 = colid
170       from syscolumns
171       where name = @col1
172           and id = object_id(@tabname)
173       if @fkey1 is NULL
174       begin
175           /*
176           ** 17563, "The table does not have a column named '%1!'."
177           */
178           raiserror 17563, @col1
179           return (1)
180       end
181   
182       -- we know we have at least 1 col in fk
183       if @col2 is not NULL
184       begin
185           select @cnt = @cnt + 1, @fkey2 = colid
186           from syscolumns
187           where name = @col2
188               and id = object_id(@tabname)
189           if @fkey2 is NULL
190           begin
191               /*
192               ** 17563, "The table does not have a column named '%1!'."
193               */
194               raiserror 17563, @col2
195               return (1)
196           end
197       end
198       else goto foreign_ok -- we know we have at least 1 col in fk
199       -- we know we have at least 2 col in fk
200   
201       if @col3 is not NULL
202       begin
203           select @cnt = @cnt + 1, @fkey3 = colid
204           from syscolumns
205           where name = @col3
206               and id = object_id(@tabname)
207           if @fkey3 is NULL
208           begin
209               /*
210               ** 17563, "The table does not have a column named '%1!'."
211               */
212               raiserror 17563, @col3
213               return (1)
214           end
215       end
216       else goto foreign_ok -- we didn't have a third key
217       -- we know we have at least 3 col in fk
218   
219       if @col4 is not NULL
220       begin
221           select @cnt = @cnt + 1, @fkey4 = colid
222           from syscolumns
223           where name = @col4
224               and id = object_id(@tabname)
225           if @fkey4 is NULL
226           begin
227               /*
228               ** 17563, "The table does not have a column named '%1!'."
229               */
230               raiserror 17563, @col4
231               return (1)
232           end
233       end
234       else goto foreign_ok -- we didn't have a forth key
235       -- you get the idea by now of the algorithm!
236   
237       if @col5 is not NULL
238       begin
239           select @cnt = @cnt + 1, @fkey5 = colid
240           from syscolumns
241           where name = @col5
242               and id = object_id(@tabname)
243           if @fkey5 is NULL
244           begin
245               /*
246               ** 17563, "The table does not have a column named '%1!'."
247               */
248               raiserror 17563, @col5
249               return (1)
250           end
251       end
252       else goto foreign_ok
253   
254       if @col6 is not NULL
255       begin
256           select @cnt = @cnt + 1, @fkey6 = colid
257           from syscolumns
258           where name = @col6
259               and id = object_id(@tabname)
260           if @fkey6 is NULL
261           begin
262               /*
263               ** 17563, "The table does not have a column named '%1!'."
264               */
265               raiserror 17563, @col6
266               return (1)
267           end
268       end
269       else goto foreign_ok
270   
271       if @col7 is not NULL
272       begin
273           select @cnt = @cnt + 1, @fkey7 = colid
274           from syscolumns
275           where name = @col7
276               and id = object_id(@tabname)
277           if @fkey7 is NULL
278           begin
279               /*
280               ** 17563, "The table does not have a column named '%1!'."
281               */
282               raiserror 17563, @col7
283               return (1)
284           end
285       end
286       else goto foreign_ok
287   
288       if @col8 is not NULL
289       begin
290           select @cnt = @cnt + 1, @fkey8 = colid
291           from syscolumns
292           where name = @col8
293               and id = object_id(@tabname)
294           if @fkey8 is NULL
295           begin
296               /*
297               ** 17563, "The table does not have a column named '%1!'."
298               */
299               raiserror 17563, @col8
300               return (1)
301           end
302       end
303   
304   
305       /*
306       **  If we made it this far then all the columns for the foreign key are ok.
307       */
308   foreign_ok:
309   
310       /*
311       **  Now let's check out the primary key that the foreign key is on.
312       **  There must be the same number of columns in the key and the
313       **  base types of the columns must agree.
314       */
315       select @pkey1 = key1, @pkey2 = key2, @pkey3 = key3, @pkey4 = key4,
316           @pkey5 = key5, @pkey6 = key6, @pkey7 = key7, @pkey8 = key8
317       from syskeys
318       where id = object_id(@pktabname)
319           and type = 1
320           and keycnt = @cnt
321   
322       /*
323       **  If @pkey1 is null then there is no such primary key or the number of
324       **  columns in the primary key are not the same as the number of columns
325       **  in the foreign key.
326       */
327       if @pkey1 is NULL
328       begin
329           if exists (select *
330                   from syskeys
331                   where id = object_id(@pktabname)
332                       and type = 1)
333           begin
334               /*
335               ** 17564, "Primary key does not exist with the same number of columns as the foreign key."
336               */
337               raiserror 17564
338           end
339           else
340           begin
341               /*
342               ** 17565, "Primary key does not exist."
343               */
344               raiserror 17565
345           end
346           return (1)
347       end
348   
349       /*
350       **  Since we have the right number of columns in the foreign and primary keys,
351       **  check that their base datatypes agree. 
352       */
353       if not exists (select *
354               from syscolumns a, syscolumns b, master.dbo.spt_values y,
355                   master.dbo.spt_values z
356               where a.colid = @fkey1
357                   and a.id = object_id(@tabname)
358                   and b.colid = @pkey1
359                   and b.id = object_id(@pktabname)
360                   and y.type = "J"
361                   and a.type = y.low
362                   and z.type = "J"
363                   and b.type = z.low
364                   and y.number = z.number)
365       begin
366           /*
367           ** 17566, "Datatypes of the column '%1!' in the keys are different."
368           */
369           raiserror 17566, @col1
370           return (1)
371       end
372   
373       /*
374       ** Now the algorithm becomes existence of col[n] requires
375       ** key to match PK [n]
376       **
377       ** again we cascade until we reach a null col[n] OR n = 8
378       **
379       ** A null col[n] means we did not specify the key (by logic above)
380       ** and hence we can progress to insert the FK.
381       ** A non-null col[n] means we have a key, but is it of the right type?
382       ** If not, raise error and return 1
383       ** Else; we have a key, its column types match the corresponding PK column
384       ** so do we have any more key columns to check?
385       */
386       if (@col2 is NULL)
387       begin
388           /* our work is done! */
389           goto doinsert
390       end
391       else /* we must check this key for consistency */
392       begin
393           if not exists (select *
394                   from syscolumns a, syscolumns b, master.dbo.spt_values y,
395                       master.dbo.spt_values z
396                   where a.colid = @fkey2
397                       and a.id = object_id(@tabname)
398                       and b.colid = @pkey2
399                       and b.id = object_id(@pktabname)
400                       and y.type = "J"
401                       and a.type = y.low
402                       and z.type = "J"
403                       and b.type = z.low
404                       and y.number = z.number)
405           begin -- it failed! Don't insert this.
406               /*
407               ** 17566, "Datatypes of the column '%1!'
408               ** in the keys are different."
409               */
410               raiserror 17566, @col2
411               return (1)
412           end
413       end -- here we need to check another key
414   
415       if (@col3 is NULL)
416       begin
417           /* our work is done! */
418           goto doinsert
419       end
420       else /* we must check this key for consistency */
421       begin
422           if not exists (select *
423                   from syscolumns a, syscolumns b, master.dbo.spt_values y,
424                       master.dbo.spt_values z
425                   where a.colid = @fkey3
426                       and a.id = object_id(@tabname)
427                       and b.colid = @pkey3
428                       and b.id = object_id(@pktabname)
429                       and y.type = "J"
430                       and a.type = y.low
431                       and z.type = "J"
432                       and b.type = z.low
433                       and y.number = z.number)
434           begin -- it failed! Don't insert this!
435               /*
436               ** 17566, "Datatypes of the column '%1!'
437               ** in the keys are different."
438               */
439               raiserror 17566, @col3
440               return (1)
441           end
442       end -- here we need to check another key
443   
444       if (@col4 is NULL)
445       begin
446           /* our work is done! */
447           goto doinsert
448       end
449       else /* we must check this key for consistency */
450       begin
451           if not exists (select *
452                   from syscolumns a, syscolumns b, master.dbo.spt_values y,
453                       master.dbo.spt_values z
454                   where a.colid = @fkey4
455                       and a.id = object_id(@tabname)
456                       and b.colid = @pkey4
457                       and b.id = object_id(@pktabname)
458                       and y.type = "J"
459                       and a.type = y.low
460                       and z.type = "J"
461                       and b.type = z.low
462                       and y.number = z.number)
463           begin -- it failed! Don't insert this.
464               /*
465               ** 17566, "Datatypes of the column '%1!'
466               ** in the keys are different."
467               */
468               raiserror 17566, @col4
469               return (1)
470           end
471       end -- here we need to check another key
472   
473       if (@col5 is NULL)
474       begin
475           /* our work is done! */
476           goto doinsert
477       end
478       else /* we must check this key for consistency */
479       begin
480           if not exists (select *
481                   from syscolumns a, syscolumns b, master.dbo.spt_values y,
482                       master.dbo.spt_values z
483                   where a.colid = @fkey5
484                       and a.id = object_id(@tabname)
485                       and b.colid = @pkey5
486                       and b.id = object_id(@pktabname)
487                       and y.type = "J"
488                       and a.type = y.low
489                       and z.type = "J"
490                       and b.type = z.low
491                       and y.number = z.number)
492           begin -- it failed! Don't insert this.
493               /*
494               ** 17566, "Datatypes of the column '%1!'
495               ** in the keys are different."
496               */
497               raiserror 17566, @col5
498               return (1)
499           end
500       end -- here we need to check another key
501   
502       if (@col6 is NULL)
503       begin
504           /* our work is done! */
505           goto doinsert
506       end
507       else /* we must check this key for consistency */
508       begin
509           if not exists (select *
510                   from syscolumns a, syscolumns b, master.dbo.spt_values y,
511                       master.dbo.spt_values z
512                   where a.colid = @fkey6
513                       and a.id = object_id(@tabname)
514                       and b.colid = @pkey6
515                       and b.id = object_id(@pktabname)
516                       and y.type = "J"
517                       and a.type = y.low
518                       and z.type = "J"
519                       and b.type = z.low
520                       and y.number = z.number)
521           begin -- it failed! Don't insert this.
522               /*
523               ** 17566, "Datatypes of the column '%1!'
524               ** in the keys are different."
525               */
526               raiserror 17566, @col6
527               return (1)
528           end
529       end -- here we need to check another key
530   
531       if (@col7 is NULL)
532       begin
533           /* our work is done! */
534           goto doinsert
535       end
536       else /* we must check this key for consistency */
537       begin
538           if not exists (select *
539                   from syscolumns a, syscolumns b, master.dbo.spt_values y,
540                       master.dbo.spt_values z
541                   where a.colid = @fkey7
542                       and a.id = object_id(@tabname)
543                       and b.colid = @pkey7
544                       and b.id = object_id(@pktabname)
545                       and y.type = "J"
546                       and a.type = y.low
547                       and z.type = "J"
548                       and b.type = z.low
549                       and y.number = z.number)
550           begin -- it failed! Don't insert this.
551               /*
552               ** 17566, "Datatypes of the column '%1!'
553               ** in the keys are different."
554               */
555               raiserror 17566, @col7
556               return (1)
557           end
558       end -- here we need to check another key
559   
560       if (@col8 is NULL)
561       begin
562           /* our work is done! */
563           goto doinsert
564       end
565       else /* we must check this key for consistency */
566       begin
567           if not exists (select *
568                   from syscolumns a, syscolumns b, master.dbo.spt_values y,
569                       master.dbo.spt_values z
570                   where a.colid = @fkey8
571                       and a.id = object_id(@tabname)
572                       and b.colid = @pkey8
573                       and b.id = object_id(@pktabname)
574                       and y.type = "J"
575                       and a.type = y.low
576                       and z.type = "J"
577                       and b.type = z.low
578                       and y.number = z.number)
579           begin -- it failed! Don't insert this.
580               /*
581               ** 17566, "Datatypes of the column '%1!'
582               ** in the keys are different."
583               */
584               raiserror 17566, @col8
585               return (1)
586           end
587       end
588   
589       /*
590       **  Everything is consistent so add the foreign key to syskeys.
591       */
592   doinsert:
593       /*
594       ** Before we insert a new foreign key relationship, check if a duplicate
595       ** one exists. If so, fail this new insert. We are allowed to only have
596       ** one foreign key relationship defined for a set of tables on the same
597       ** set of columns.
598       */
599       if exists (select 1 from syskeys
600               where id = object_id(@tabname)
601                   and type = 2
602                   and depid = object_id(@pktabname)
603                   and keycnt = @cnt
604                   and size = 0
605                   and key1 = @fkey1
606                   and key2 = @fkey2
607                   and key3 = @fkey3
608                   and key4 = @fkey4
609                   and key5 = @fkey5
610                   and key6 = @fkey6
611                   and key7 = @fkey7
612                   and key8 = @fkey8
613   
614                   and depkey1 = @pkey1
615                   and depkey2 = @pkey2
616                   and depkey3 = @pkey3
617                   and depkey4 = @pkey4
618                   and depkey5 = @pkey5
619                   and depkey6 = @pkey6
620                   and depkey7 = @pkey7
621                   and depkey8 = @pkey8
622               )
623       begin
624           raiserror 17569, @tabname, @pktabname
625           return (1)
626       end
627   
628       /* 
629       ** This transaction also writes a log record for replicating the
630       ** invocation of this procedure. If logexec() fails, the transaction	
631       ** is aborted.
632       **
633       ** IMPORTANT: The name rs_logexec is significant and is used by
634       ** Replication Server.
635       */
636       begin transaction rs_logexec
637   
638       insert syskeys(id, type, depid, keycnt, size, key1, key2, key3, key4, key5,
639           key6, key7, key8, depkey1, depkey2, depkey3, depkey4, depkey5,
640           depkey6, depkey7, depkey8, spare1)
641       values (object_id(@tabname), 2, object_id(@pktabname), @cnt, 0, @fkey1,
642           @fkey2, @fkey3, @fkey4, @fkey5,
643           @fkey6, @fkey7, @fkey8, @pkey1, @pkey2, @pkey3, @pkey4, @pkey5,
644           @pkey6, @pkey7, @pkey8, 0)
645   
646       if @@error != 0
647       begin
648           rollback transaction rs_logexec
649           return (1)
650       end
651   
652       /*
653       ** Write the log record to replicate this invocation 
654       ** of the stored procedure.
655       */
656       if (logexec() != 1)
657       begin
658           /*
659           ** 17756, "The execution of the stored procedure '%1!' in
660           **         database '%2!' was aborted because there was an
661           **         error in writing the replication log record."
662           */
663           select @dbname = db_name()
664           raiserror 17756, "sp_foreignkey", @dbname
665   
666           rollback transaction rs_logexec
667           return (1)
668       end
669   
670       commit transaction
671   
672       /* 
673       ** 17567, "New foreign key added."
674       */
675       exec sp_getmessage 17567, @msg output
676       print @msg
677   
678       return (0)
679   


exec sp_procxmode 'sp_foreignkey', 'AnyMode'
go

Grant Execute on sp_foreignkey to public
go
DEFECTS
 QJWI 5 Join or Sarg Without Index 361
 QJWI 5 Join or Sarg Without Index 363
 QJWI 5 Join or Sarg Without Index 401
 QJWI 5 Join or Sarg Without Index 403
 QJWI 5 Join or Sarg Without Index 430
 QJWI 5 Join or Sarg Without Index 432
 QJWI 5 Join or Sarg Without Index 459
 QJWI 5 Join or Sarg Without Index 461
 QJWI 5 Join or Sarg Without Index 488
 QJWI 5 Join or Sarg Without Index 490
 QJWI 5 Join or Sarg Without Index 517
 QJWI 5 Join or Sarg Without Index 519
 QJWI 5 Join or Sarg Without Index 546
 QJWI 5 Join or Sarg Without Index 548
 QJWI 5 Join or Sarg Without Index 575
 QJWI 5 Join or Sarg Without Index 577
 MTYP 4 Assignment type mismatch key1: smallint = int 641
 MTYP 4 Assignment type mismatch key2: smallint = int 642
 MTYP 4 Assignment type mismatch key3: smallint = int 642
 MTYP 4 Assignment type mismatch key4: smallint = int 642
 MTYP 4 Assignment type mismatch key5: smallint = int 642
 MTYP 4 Assignment type mismatch depkey1: smallint = int 643
 MTYP 4 Assignment type mismatch depkey2: smallint = int 643
 MTYP 4 Assignment type mismatch depkey3: smallint = int 643
 MTYP 4 Assignment type mismatch depkey4: smallint = int 643
 MTYP 4 Assignment type mismatch depkey5: smallint = int 643
 MTYP 4 Assignment type mismatch key6: smallint = int 643
 MTYP 4 Assignment type mismatch key7: smallint = int 643
 MTYP 4 Assignment type mismatch key8: smallint = int 643
 MTYP 4 Assignment type mismatch depkey6: smallint = int 644
 MTYP 4 Assignment type mismatch depkey7: smallint = int 644
 MTYP 4 Assignment type mismatch depkey8: smallint = int 644
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 319
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 332
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 356
 QTYP 4 Comparison type mismatch smallint = int 356
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 358
 QTYP 4 Comparison type mismatch smallint = int 358
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 361
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 363
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 396
 QTYP 4 Comparison type mismatch smallint = int 396
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 398
 QTYP 4 Comparison type mismatch smallint = int 398
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 401
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 403
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 425
 QTYP 4 Comparison type mismatch smallint = int 425
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 427
 QTYP 4 Comparison type mismatch smallint = int 427
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 430
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 432
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 454
 QTYP 4 Comparison type mismatch smallint = int 454
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 456
 QTYP 4 Comparison type mismatch smallint = int 456
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 459
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 461
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 483
 QTYP 4 Comparison type mismatch smallint = int 483
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 485
 QTYP 4 Comparison type mismatch smallint = int 485
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 488
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 490
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 512
 QTYP 4 Comparison type mismatch smallint = int 512
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 514
 QTYP 4 Comparison type mismatch smallint = int 514
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 517
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 519
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 541
 QTYP 4 Comparison type mismatch smallint = int 541
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 543
 QTYP 4 Comparison type mismatch smallint = int 543
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 546
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 548
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 570
 QTYP 4 Comparison type mismatch smallint = int 570
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 572
 QTYP 4 Comparison type mismatch smallint = int 572
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 575
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 577
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 601
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 605
 QTYP 4 Comparison type mismatch smallint = int 605
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 606
 QTYP 4 Comparison type mismatch smallint = int 606
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 607
 QTYP 4 Comparison type mismatch smallint = int 607
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 608
 QTYP 4 Comparison type mismatch smallint = int 608
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 609
 QTYP 4 Comparison type mismatch smallint = int 609
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 610
 QTYP 4 Comparison type mismatch smallint = int 610
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 611
 QTYP 4 Comparison type mismatch smallint = int 611
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 612
 QTYP 4 Comparison type mismatch smallint = int 612
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 614
 QTYP 4 Comparison type mismatch smallint = int 614
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 615
 QTYP 4 Comparison type mismatch smallint = int 615
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 616
 QTYP 4 Comparison type mismatch smallint = int 616
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 617
 QTYP 4 Comparison type mismatch smallint = int 617
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 618
 QTYP 4 Comparison type mismatch smallint = int 618
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 619
 QTYP 4 Comparison type mismatch smallint = int 619
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 620
 QTYP 4 Comparison type mismatch smallint = int 620
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 621
 QTYP 4 Comparison type mismatch smallint = int 621
 TNOU 4 Table with no unique index master..spt_values master..spt_values
 TNOU 4 Table with no unique index sybsystemprocs..syskeys sybsystemprocs..syskeys
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 112
 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 145
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 172
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 188
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 206
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 224
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 242
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 259
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 276
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 293
 MAW1 3 Warning message on %name% sybsystemprocs..syskeys.id: Warning message on syskeys 318
 MAW1 3 Warning message on %name% sybsystemprocs..syskeys.id: Warning message on syskeys 331
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 357
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 359
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 397
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 399
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 426
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 428
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 455
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 457
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 484
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 486
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 513
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 515
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 542
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 544
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 571
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 573
 MAW1 3 Warning message on %name% sybsystemprocs..syskeys.id: Warning message on syskeys 600
 MAW1 3 Warning message on %name% sybsystemprocs..syskeys.id: Warning message on syskeys 638
 MGTP 3 Grant to public master..spt_values  
 MGTP 3 Grant to public sybsystemprocs..sp_foreignkey  
 MGTP 3 Grant to public sybsystemprocs..syscolumns  
 MGTP 3 Grant to public sybsystemprocs..syskeys  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MNER 3 No Error Check should check return value of exec 675
 MUCO 3 Useless Code Useless Brackets 89
 MUCO 3 Useless Code Useless Brackets 100
 MUCO 3 Useless Code Useless Brackets 124
 MUCO 3 Useless Code Useless Brackets 137
 MUCO 3 Useless Code Useless Brackets 156
 MUCO 3 Useless Code Useless Brackets 179
 MUCO 3 Useless Code Useless Brackets 195
 MUCO 3 Useless Code Useless Brackets 213
 MUCO 3 Useless Code Useless Brackets 231
 MUCO 3 Useless Code Useless Brackets 249
 MUCO 3 Useless Code Useless Brackets 266
 MUCO 3 Useless Code Useless Brackets 283
 MUCO 3 Useless Code Useless Brackets 300
 MUCO 3 Useless Code Useless Brackets 346
 MUCO 3 Useless Code Useless Brackets 370
 MUCO 3 Useless Code Useless Brackets 386
 MUCO 3 Useless Code Useless Brackets 411
 MUCO 3 Useless Code Useless Brackets 415
 MUCO 3 Useless Code Useless Brackets 440
 MUCO 3 Useless Code Useless Brackets 444
 MUCO 3 Useless Code Useless Brackets 469
 MUCO 3 Useless Code Useless Brackets 473
 MUCO 3 Useless Code Useless Brackets 498
 MUCO 3 Useless Code Useless Brackets 502
 MUCO 3 Useless Code Useless Brackets 527
 MUCO 3 Useless Code Useless Brackets 531
 MUCO 3 Useless Code Useless Brackets 556
 MUCO 3 Useless Code Useless Brackets 560
 MUCO 3 Useless Code Useless Brackets 585
 MUCO 3 Useless Code Useless Brackets 625
 MUCO 3 Useless Code Useless Brackets 649
 MUCO 3 Useless Code Useless Brackets 656
 MUCO 3 Useless Code Useless Brackets 667
 MUCO 3 Useless Code Useless Brackets 678
 QAFM 3 Var Assignment from potentially many rows 169
 QAFM 3 Var Assignment from potentially many rows 185
 QAFM 3 Var Assignment from potentially many rows 203
 QAFM 3 Var Assignment from potentially many rows 221
 QAFM 3 Var Assignment from potentially many rows 239
 QAFM 3 Var Assignment from potentially many rows 256
 QAFM 3 Var Assignment from potentially many rows 273
 QAFM 3 Var Assignment from potentially many rows 290
 QAFM 3 Var Assignment from potentially many rows 315
 QISO 3 Set isolation level 76
 QNAJ 3 Not using ANSI Inner Join 354
 QNAJ 3 Not using ANSI Inner Join 394
 QNAJ 3 Not using ANSI Inner Join 423
 QNAJ 3 Not using ANSI Inner Join 452
 QNAJ 3 Not using ANSI Inner Join 481
 QNAJ 3 Not using ANSI Inner Join 510
 QNAJ 3 Not using ANSI Inner Join 539
 QNAJ 3 Not using ANSI Inner Join 568
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
171
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
187
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
205
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
223
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
241
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
258
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
275
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
292
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {colid, id}
356
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {colid, id}
358
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {colid, id}
396
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {colid, id}
398
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {colid, id}
425
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {colid, id}
427
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {colid, id}
454
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {colid, id}
456
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {colid, id}
483
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {colid, id}
485
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {colid, id}
512
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {colid, id}
514
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {colid, id}
541
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {colid, id}
543
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {colid, id}
570
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {colid, id}
572
 QSWV 3 Sarg with variable @cnt, Candidate Index: syskeys.csyskeys clustered(id) S 320
 QSWV 3 Sarg with variable @cnt, Candidate Index: syskeys.csyskeys clustered(id) S 603
 QSWV 3 Sarg with variable @fkey1, Candidate Index: syskeys.csyskeys clustered(id) S 605
 QSWV 3 Sarg with variable @fkey2, Candidate Index: syskeys.csyskeys clustered(id) S 606
 QSWV 3 Sarg with variable @fkey3, Candidate Index: syskeys.csyskeys clustered(id) S 607
 QSWV 3 Sarg with variable @fkey4, Candidate Index: syskeys.csyskeys clustered(id) S 608
 QSWV 3 Sarg with variable @fkey5, Candidate Index: syskeys.csyskeys clustered(id) S 609
 QSWV 3 Sarg with variable @fkey6, Candidate Index: syskeys.csyskeys clustered(id) S 610
 QSWV 3 Sarg with variable @fkey7, Candidate Index: syskeys.csyskeys clustered(id) S 611
 QSWV 3 Sarg with variable @fkey8, Candidate Index: syskeys.csyskeys clustered(id) S 612
 QSWV 3 Sarg with variable @pkey1, Candidate Index: syskeys.csyskeys clustered(id) S 614
 QSWV 3 Sarg with variable @pkey2, Candidate Index: syskeys.csyskeys clustered(id) S 615
 QSWV 3 Sarg with variable @pkey3, Candidate Index: syskeys.csyskeys clustered(id) S 616
 QSWV 3 Sarg with variable @pkey4, Candidate Index: syskeys.csyskeys clustered(id) S 617
 QSWV 3 Sarg with variable @pkey5, Candidate Index: syskeys.csyskeys clustered(id) S 618
 QSWV 3 Sarg with variable @pkey6, Candidate Index: syskeys.csyskeys clustered(id) S 619
 QSWV 3 Sarg with variable @pkey7, Candidate Index: syskeys.csyskeys clustered(id) S 620
 QSWV 3 Sarg with variable @pkey8, Candidate Index: syskeys.csyskeys clustered(id) S 621
 MSUB 2 Subquery Marker 110
 MSUB 2 Subquery Marker 126
 MSUB 2 Subquery Marker 329
 MSUB 2 Subquery Marker 353
 MSUB 2 Subquery Marker 393
 MSUB 2 Subquery Marker 422
 MSUB 2 Subquery Marker 451
 MSUB 2 Subquery Marker 480
 MSUB 2 Subquery Marker 509
 MSUB 2 Subquery Marker 538
 MSUB 2 Subquery Marker 567
 MSUB 2 Subquery Marker 599
 MTR1 2 Metrics: Comments Ratio Comments: 42% 33
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 124 = 148dec - 26exi + 2 33
 MTR3 2 Metrics: Query Complexity Complexity: 366 33
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, c2=sybsystemprocs..syscolumns, sv=master..spt_values, sv2=master..spt_values} 0 353
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, c2=sybsystemprocs..syscolumns, sv=master..spt_values, sv2=master..spt_values} 0 393
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, c2=sybsystemprocs..syscolumns, sv=master..spt_values, sv2=master..spt_values} 0 422
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, c2=sybsystemprocs..syscolumns, sv=master..spt_values, sv2=master..spt_values} 0 451
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, c2=sybsystemprocs..syscolumns, sv=master..spt_values, sv2=master..spt_values} 0 480
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, c2=sybsystemprocs..syscolumns, sv=master..spt_values, sv2=master..spt_values} 0 509
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, c2=sybsystemprocs..syscolumns, sv=master..spt_values, sv2=master..spt_values} 0 538
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, c2=sybsystemprocs..syscolumns, sv=master..spt_values, sv2=master..spt_values} 0 567

DATA PROPAGATION detailed
ColumnWritten To
@pktabnamesyskeys.depid  
@tabnamesyskeys.id  

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..syscolumns  
calls proc sybsystemprocs..sp_getmessage  
   reads table master..syslanguages (1)  
   reads table sybsystemprocs..sysusermessages  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..sysmessages (1)  
reads table master..spt_values (1)  
reads table sybsystemprocs..sysobjects  
read_writes table sybsystemprocs..syskeys