DatabaseProcApplicationCreatedLinks
sybsystemprocssp_commonkey  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/commonkey */
4     /*
5     ** Messages for "sp_commonkey"          17390
6     **
7     ** 17390, "Table or view name must be in 'current' database."
8     ** 17391, "First table in the common key doesn't exist."
9     ** 17392, "Second table in the common key doesn't exist."
10    ** 17393, "Only the table owner may define its common keys."
11    ** 17394, "The tables have no such first column or the columns are of different types."                     
12    ** 17395, "The tables have no such second column or the columns are of different types."                    
13    ** 17396, "The tables have no such third column or the columns are of different types."                     
14    ** 17397, "The tables have no such fourth column or the columns are of different types."
15    ** 17398, "The tables have no such fifth column or the columns are of different types."
16    ** 17399, "The tables have no such sixth column or the columns are of different types."
17    ** 17400, "The tables have no such seventh column or the columns are of different types."
18    ** 17401, "The tables have no such eighth column or the columns are of different types."
19    ** 17402, "New common key added."
20    ** 17756, "The execution of the stored procedure '%1!' in database
21    **         '%2!' was aborted because there was an error in writing the
22    **         replication log record."
23    ** 17403, "Common key definition already exists between the tables '%1!' and table '%2!' for the specified columns."
24    */
25    
26    
27    /*
28    ** IMPORTANT NOTE:
29    ** This stored procedure uses the built-in function object_id() in the
30    ** where clause of a select query. If you intend to change this query
31    ** or use the object_id() or db_id() builtin in this procedure, please read the
32    ** READ.ME file in the $DBMS/generic/sproc directory to ensure that the rules
33    ** pertaining to object-id's and db-id's outlined there, are followed.
34    */
35    
36    create or replace procedure sp_commonkey
37        @tabaname varchar(767), /* name of first table in the key */
38        @tabbname varchar(767), /* name of second table in the key */
39        @col1a varchar(255), /* first column name of first table */
40        @col1b varchar(255), /* first column name of second table */
41        @col2a varchar(255) = NULL,
42        @col2b varchar(255) = NULL,
43        @col3a varchar(255) = NULL,
44        @col3b varchar(255) = NULL,
45        @col4a varchar(255) = NULL,
46        @col4b varchar(255) = NULL,
47        @col5a varchar(255) = NULL,
48        @col5b varchar(255) = NULL,
49        @col6a varchar(255) = NULL,
50        @col6b varchar(255) = NULL,
51        @col7a varchar(255) = NULL,
52        @col7b varchar(255) = NULL,
53        @col8a varchar(255) = NULL,
54        @col8b varchar(255) = NULL
55    as
56    
57        declare @uida int /* owner of the first table */
58        declare @uidb int /* owner of the second table */
59        declare @cnt int /* how many columns are in the key */
60        declare @msg varchar(1024)
61    
62        declare @key1a int /* colids of the first table */
63        declare @key2a int
64        declare @key3a int
65        declare @key4a int
66        declare @key5a int
67        declare @key6a int
68        declare @key7a int
69        declare @key8a int
70    
71        declare @key1b int /* colids of the second table */
72        declare @key2b int
73        declare @key3b int
74        declare @key4b int
75        declare @key5b int
76        declare @key6b int
77        declare @key7b int
78        declare @key8b int
79        declare @dbname varchar(255)
80    
81    
82        if @@trancount = 0
83        begin
84            set chained off
85        end
86    
87        set transaction isolation level 1
88    
89        /*
90        **  Check to see that the tabnames are local.
91        */
92        if @tabaname like "%.%.%"
93        begin
94            if substring(@tabaname, 1, charindex(".", @tabaname) - 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       if @tabbname like "%.%.%"
104       begin
105           if substring(@tabbname, 1, charindex(".", @tabbname) - 1) != db_name()
106           begin
107               /*
108               ** 17390, "Table or view name must be in 'current' database."
109               */
110               raiserror 17390
111               return (1)
112           end
113       end
114   
115       /*
116       **  See if we can find the objects.  They must be a system table, user table,
117       **  or view.  The low 3 bits of sysobjects.sysstat indicate what the 
118       **  object type is -- it's more reliable than using sysobjects.type which
119       **  could change.
120       */
121       if not exists (select *
122               from sysobjects
123               where id = object_id(@tabaname)
124                   and (sysstat & 7 = 1 /* system table */
125                       or sysstat & 7 = 2 /* view */
126                       or sysstat & 7 = 3)) /* user table */
127       /*
128       **  If either of the tables don't exist, quit.
129       */
130       begin
131           /*
132           ** 17391, "First table in the common key doesn't exist."
133           */
134           raiserror 17391
135           return (1)
136       end
137       if not exists (select id
138               from sysobjects
139               where id = object_id(@tabbname)
140                   and (sysstat & 7 = 1
141                       or sysstat & 7 = 2
142                       or sysstat & 7 = 3))
143       begin
144           /*
145           ** 17392, "Second table in the common key doesn't exist."
146           */
147           raiserror 17392
148           return (1)
149       end
150   
151       /*
152       **  In order to define a commonkey, the user must be the owner of one of
153       **  the tables.
154       */
155       select @uida = uid
156       from sysobjects
157       where id = object_id(@tabaname)
158           and (sysstat & 7 = 1 /* system table */
159               or sysstat & 7 = 2 /* view */
160               or sysstat & 7 = 3) /* user table */
161   
162       select @uidb = uid
163       from sysobjects
164       where id = object_id(@tabbname)
165           and (sysstat & 7 = 1 /* system table */
166               or sysstat & 7 = 2 /* view */
167               or sysstat & 7 = 3) /* user table */
168   
169       if (@uida != user_id() and @uidb != user_id())
170       begin
171           /*
172           ** 17393, "Only the table owner may define its common keys."
173           */
174           raiserror 17393
175           return (1)
176       end
177   
178       /*
179       **  Now check to see that the first key columns exist and have compatible types.
180       */
181       select @cnt = 1, @key1a = a.colid, @key1b = b.colid
182       from syscolumns a, syscolumns b, master.dbo.spt_values y,
183           master.dbo.spt_values z
184       where a.name = @col1a
185           and a.id = object_id(@tabaname)
186           and b.name = @col1b
187           and b.id = object_id(@tabbname)
188           and y.type = "J"
189           and a.type = y.low
190           and z.type = "J"
191           and b.type = z.low
192           and y.number = z.number
193       if @key1a is NULL
194       begin
195           /*
196           ** 17394, "The tables have no such first column or the columns are of different types."
197           */
198           raiserror 17394
199           return (1)
200       end
201   
202       if @col2a is not NULL
203       begin
204           select @cnt = @cnt + 1, @key2a = a.colid, @key2b = b.colid
205           from syscolumns a, syscolumns b, master.dbo.spt_values y,
206               master.dbo.spt_values z
207           where a.name = @col2a
208               and a.id = object_id(@tabaname)
209               and b.name = @col2b
210               and b.id = object_id(@tabbname)
211               and y.type = "J"
212               and a.type = y.low
213               and z.type = "J"
214               and b.type = z.low
215               and y.number = z.number
216           if @key2a is NULL
217           begin
218               /*
219               ** 17395, "The tables have no such second column or the columns are of different types."
220               */
221               raiserror 17395
222               return (1)
223           end
224       end
225       else goto keys_ok
226   
227       if @col3a is not NULL
228       begin
229           select @cnt = @cnt + 1, @key3a = a.colid, @key3b = b.colid
230           from syscolumns a, syscolumns b, master.dbo.spt_values y,
231               master.dbo.spt_values z
232           where a.name = @col3a
233               and a.id = object_id(@tabaname)
234               and b.name = @col3b
235               and b.id = object_id(@tabbname)
236               and y.type = "J"
237               and a.type = y.low
238               and z.type = "J"
239               and b.type = z.low
240               and y.number = z.number
241           if @key3a is NULL
242           begin
243               /*
244               ** 17396, "The tables have no such third column or the columns are of different types."                     
245               */
246               raiserror 17396
247               return (1)
248           end
249       end
250       else goto keys_ok
251   
252       if @col4a is not NULL
253       begin
254           select @cnt = @cnt + 1, @key4a = a.colid, @key4b = b.colid
255           from syscolumns a, syscolumns b, master.dbo.spt_values y,
256               master.dbo.spt_values z
257           where a.name = @col4a
258               and a.id = object_id(@tabaname)
259               and b.name = @col4b
260               and b.id = object_id(@tabbname)
261               and y.type = "J"
262               and a.type = y.low
263               and z.type = "J"
264               and b.type = z.low
265               and y.number = z.number
266           if @key4a is NULL
267           begin
268               /*
269               ** 17397, "The tables have no such fourth column or the columns are of different types."
270               */
271               raiserror 17397
272               return (1)
273           end
274       end
275       else goto keys_ok
276   
277       if @col5a is not NULL
278       begin
279           select @cnt = @cnt + 1, @key5a = a.colid, @key5b = b.colid
280           from syscolumns a, syscolumns b, master.dbo.spt_values y,
281               master.dbo.spt_values z
282           where a.name = @col5a
283               and a.id = object_id(@tabaname)
284               and b.name = @col5b
285               and b.id = object_id(@tabbname)
286               and y.type = "J"
287               and a.type = y.low
288               and z.type = "J"
289               and b.type = z.low
290               and y.number = z.number
291           if @key5a is NULL
292           begin
293               /*
294               ** 17398, "The tables have no such fifth column or the columns are of different types."
295               */
296               raiserror 17398
297               return (1)
298           end
299       end
300       else goto keys_ok
301   
302       if @col6a is not NULL
303       begin
304           select @cnt = @cnt + 1, @key6a = a.colid, @key6b = b.colid
305           from syscolumns a, syscolumns b, master.dbo.spt_values y,
306               master.dbo.spt_values z
307           where a.name = @col6a
308               and a.id = object_id(@tabaname)
309               and b.name = @col6b
310               and b.id = object_id(@tabbname)
311               and y.type = "J"
312               and a.type = y.low
313               and z.type = "J"
314               and b.type = z.low
315               and y.number = z.number
316           if @key6a IS NULL
317           begin
318               /*
319               ** 17399, "The tables have no such sixth column or the columns are of different types."
320               */
321               raiserror 17399
322               return (1)
323           end
324       end
325       else goto keys_ok
326   
327       if @col7a is not NULL
328       begin
329           select @cnt = @cnt + 1, @key7a = a.colid, @key7b = b.colid
330           from syscolumns a, syscolumns b, master.dbo.spt_values y,
331               master.dbo.spt_values z
332           where a.name = @col7a
333               and a.id = object_id(@tabaname)
334               and b.name = @col7b
335               and b.id = object_id(@tabbname)
336               and y.type = "J"
337               and a.type = y.low
338               and z.type = "J"
339               and b.type = z.low
340               and y.number = z.number
341           if @key7a is NULL
342           begin
343               /*
344               ** 17400, "The tables have no such seventh column or the columns are of different types."
345               */
346               raiserror 17400
347               return (1)
348           end
349       end
350       else goto keys_ok
351   
352       if @col8a is not NULL
353       begin
354           select @cnt = @cnt + 1, @key8a = a.colid, @key8b = b.colid
355           from syscolumns a, syscolumns b, master.dbo.spt_values y,
356               master.dbo.spt_values z
357           where a.name = @col8a
358               and a.id = object_id(@tabaname)
359               and b.name = @col8b
360               and b.id = object_id(@tabbname)
361               and y.type = "J"
362               and a.type = y.low
363               and z.type = "J"
364               and b.type = z.low
365               and y.number = z.number
366           if @key8a is NULL
367           begin
368               /*
369               ** 17401, "The tables have no such eighth column or the columns are of different types."
370               */
371               raiserror 17401
372               return (1)
373           end
374       end
375   
376       /*
377       **  If we made it this far then all the columns for the common key are ok.
378       **  Everything is consistent so add the common key to syskeys.
379       */
380   keys_ok:
381   
382       /*
383       ** Before we insert a new common key relationship, check if a duplicate
384       ** one exists. If so, fail this new insert. We are allowed to only have
385       ** one common key relationship defined for a set of tables on the same
386       ** set of columns. In case of common key relationships, users can define
387       ** one even on a table that they don't own. So we have to check for the
388       ** existence of a row for both 'tabaname' and 'tabbname'. [ See below where
389       ** the INSERT is done. ]
390       */
391       /*
392       ** User owns 'tableA'. So if there was a relationship defined earlier, it
393       ** would have been [from id] tableA -> [to depid] tableB. Check if one 
394       ** such row exists.
395       */
396       if (@uida = user_id())
397       begin
398           if exists (select 1 from syskeys
399                   where id = object_id(@tabaname)
400                       and type = 3
401                       and depid = object_id(@tabbname)
402                       and keycnt = @cnt
403                       and size = 0
404                       and key1 = @key1a
405                       and key2 = @key2a
406                       and key3 = @key3a
407                       and key4 = @key4a
408                       and key5 = @key5a
409                       and key6 = @key6a
410                       and key7 = @key7a
411                       and key8 = @key8a
412   
413                       and depkey1 = @key1b
414                       and depkey2 = @key2b
415                       and depkey3 = @key3b
416                       and depkey4 = @key4b
417                       and depkey5 = @key5b
418                       and depkey6 = @key6b
419                       and depkey7 = @key7b
420                       and depkey8 = @key8b
421                   )
422           begin
423               raiserror 17403, @tabaname, @tabbname
424               return (1)
425           end
426       end
427       /*
428       ** User owns 'tableB'. So if there was a relationship defined earlier, it
429       ** would have been [from id] tableB -> [to depid] tableA. Check if one
430       ** such row exists.
431       */
432       else if exists (select 1 from syskeys
433               where id = object_id(@tabbname)
434                   and type = 3
435                   and depid = object_id(@tabaname)
436                   and keycnt = @cnt
437                   and size = 0
438                   and key1 = @key1b
439                   and key2 = @key2b
440                   and key3 = @key3b
441                   and key4 = @key4b
442                   and key5 = @key5b
443                   and key6 = @key6b
444                   and key7 = @key7b
445                   and key8 = @key8b
446   
447                   and depkey1 = @key1a
448                   and depkey2 = @key2a
449                   and depkey3 = @key3a
450                   and depkey4 = @key4a
451                   and depkey5 = @key5a
452                   and depkey6 = @key6a
453                   and depkey7 = @key7a
454                   and depkey8 = @key8a
455               )
456       begin
457           raiserror 17403, @tabbname, @tabaname
458           return (1)
459       end
460   
461       /* 
462       ** This transaction also writes a log record for replicating the
463       ** invocation of this procedure. If logexec() fails, the transaction	
464       ** is aborted.
465       **
466       ** IMPORTANT: The name rs_logexec is significant and is used by
467       ** Replication Server.
468       */
469       begin transaction rs_logexec
470   
471       if @uida = user_id()
472       begin
473           insert syskeys(id, type, depid, keycnt, size,
474               key1, key2, key3, key4, key5, key6, key7, key8,
475               depkey1, depkey2, depkey3, depkey4, depkey5, depkey6,
476               depkey7, depkey8, spare1)
477           values (object_id(@tabaname), 3, object_id(@tabbname), @cnt, 0,
478               @key1a, @key2a, @key3a, @key4a, @key5a, @key6a, @key7a, @key8a,
479               @key1b, @key2b, @key3b, @key4b, @key5b, @key6b,
480               @key7b, @key8b, 0)
481       end
482       else
483       begin
484   
485           /*
486           **  The user owns the second table but not the first.  In this case, we need
487           **  to invert the entries inserted into syskeys.  This is done to
488           **  keep things consistent for the sp_dropkey procedure.
489           */
490           insert syskeys(id, type, depid, keycnt, size,
491               key1, key2, key3, key4, key5, key6, key7, key8,
492               depkey1, depkey2, depkey3, depkey4, depkey5, depkey6,
493               depkey7, depkey8, spare1)
494           values (object_id(@tabbname), 3, object_id(@tabaname), @cnt, 0,
495               @key1b, @key2b, @key3b, @key4b, @key5b, @key6b, @key7b, @key8b,
496               @key1a, @key2a, @key3a, @key4a, @key5a, @key6a,
497               @key7a, @key8a, 0)
498       end
499   
500       if (@@error != 0)
501       begin
502           rollback transaction rs_logexec
503           return (1)
504       end
505   
506       /*
507       ** Write the log record to replicate this invocation 
508       ** of the stored procedure.
509       */
510       if (logexec() != 1)
511       begin
512           /*
513           ** 17756, "The execution of the stored procedure '%1!' in
514           **         database '%2!' was aborted because there was an
515           **         error in writing the replication log record."
516           */
517           select @dbname = db_name()
518           raiserror 17756, "sp_commonkey", @dbname
519   
520           rollback transaction rs_logexec
521           return (1)
522       end
523   
524       commit transaction
525   
526       /*
527       ** 17402, "New common key added."
528       */
529       exec sp_getmessage 17402, @msg output
530       print @msg
531   
532       return (0)
533   
534   


exec sp_procxmode 'sp_commonkey', 'AnyMode'
go

Grant Execute on sp_commonkey to public
go
DEFECTS
 QJWI 5 Join or Sarg Without Index 189
 QJWI 5 Join or Sarg Without Index 191
 QJWI 5 Join or Sarg Without Index 212
 QJWI 5 Join or Sarg Without Index 214
 QJWI 5 Join or Sarg Without Index 237
 QJWI 5 Join or Sarg Without Index 239
 QJWI 5 Join or Sarg Without Index 262
 QJWI 5 Join or Sarg Without Index 264
 QJWI 5 Join or Sarg Without Index 287
 QJWI 5 Join or Sarg Without Index 289
 QJWI 5 Join or Sarg Without Index 312
 QJWI 5 Join or Sarg Without Index 314
 QJWI 5 Join or Sarg Without Index 337
 QJWI 5 Join or Sarg Without Index 339
 QJWI 5 Join or Sarg Without Index 362
 QJWI 5 Join or Sarg Without Index 364
 MTYP 4 Assignment type mismatch key1: smallint = int 478
 MTYP 4 Assignment type mismatch key2: smallint = int 478
 MTYP 4 Assignment type mismatch key3: smallint = int 478
 MTYP 4 Assignment type mismatch key4: smallint = int 478
 MTYP 4 Assignment type mismatch key5: smallint = int 478
 MTYP 4 Assignment type mismatch key6: smallint = int 478
 MTYP 4 Assignment type mismatch key7: smallint = int 478
 MTYP 4 Assignment type mismatch key8: smallint = int 478
 MTYP 4 Assignment type mismatch depkey1: smallint = int 479
 MTYP 4 Assignment type mismatch depkey2: smallint = int 479
 MTYP 4 Assignment type mismatch depkey3: smallint = int 479
 MTYP 4 Assignment type mismatch depkey4: smallint = int 479
 MTYP 4 Assignment type mismatch depkey5: smallint = int 479
 MTYP 4 Assignment type mismatch depkey6: smallint = int 479
 MTYP 4 Assignment type mismatch depkey7: smallint = int 480
 MTYP 4 Assignment type mismatch depkey8: smallint = int 480
 MTYP 4 Assignment type mismatch key1: smallint = int 495
 MTYP 4 Assignment type mismatch key2: smallint = int 495
 MTYP 4 Assignment type mismatch key3: smallint = int 495
 MTYP 4 Assignment type mismatch key4: smallint = int 495
 MTYP 4 Assignment type mismatch key5: smallint = int 495
 MTYP 4 Assignment type mismatch key6: smallint = int 495
 MTYP 4 Assignment type mismatch key7: smallint = int 495
 MTYP 4 Assignment type mismatch key8: smallint = int 495
 MTYP 4 Assignment type mismatch depkey1: smallint = int 496
 MTYP 4 Assignment type mismatch depkey2: smallint = int 496
 MTYP 4 Assignment type mismatch depkey3: smallint = int 496
 MTYP 4 Assignment type mismatch depkey4: smallint = int 496
 MTYP 4 Assignment type mismatch depkey5: smallint = int 496
 MTYP 4 Assignment type mismatch depkey6: smallint = int 496
 MTYP 4 Assignment type mismatch depkey7: smallint = int 497
 MTYP 4 Assignment type mismatch depkey8: smallint = int 497
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 189
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 191
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 212
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 214
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 237
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 239
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 262
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 264
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 287
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 289
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 312
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 314
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 337
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 339
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 362
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 364
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 400
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 404
 QTYP 4 Comparison type mismatch smallint = int 404
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 405
 QTYP 4 Comparison type mismatch smallint = int 405
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 406
 QTYP 4 Comparison type mismatch smallint = int 406
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 407
 QTYP 4 Comparison type mismatch smallint = int 407
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 408
 QTYP 4 Comparison type mismatch smallint = int 408
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 409
 QTYP 4 Comparison type mismatch smallint = int 409
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 410
 QTYP 4 Comparison type mismatch smallint = int 410
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 411
 QTYP 4 Comparison type mismatch smallint = int 411
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 413
 QTYP 4 Comparison type mismatch smallint = int 413
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 414
 QTYP 4 Comparison type mismatch smallint = int 414
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 415
 QTYP 4 Comparison type mismatch smallint = int 415
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 416
 QTYP 4 Comparison type mismatch smallint = int 416
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 417
 QTYP 4 Comparison type mismatch smallint = int 417
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 418
 QTYP 4 Comparison type mismatch smallint = int 418
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 419
 QTYP 4 Comparison type mismatch smallint = int 419
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 420
 QTYP 4 Comparison type mismatch smallint = int 420
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 434
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 438
 QTYP 4 Comparison type mismatch smallint = int 438
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 439
 QTYP 4 Comparison type mismatch smallint = int 439
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 440
 QTYP 4 Comparison type mismatch smallint = int 440
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 441
 QTYP 4 Comparison type mismatch smallint = int 441
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 442
 QTYP 4 Comparison type mismatch smallint = int 442
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 443
 QTYP 4 Comparison type mismatch smallint = int 443
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 444
 QTYP 4 Comparison type mismatch smallint = int 444
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 445
 QTYP 4 Comparison type mismatch smallint = int 445
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 447
 QTYP 4 Comparison type mismatch smallint = int 447
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 448
 QTYP 4 Comparison type mismatch smallint = int 448
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 449
 QTYP 4 Comparison type mismatch smallint = int 449
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 450
 QTYP 4 Comparison type mismatch smallint = int 450
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 451
 QTYP 4 Comparison type mismatch smallint = int 451
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 452
 QTYP 4 Comparison type mismatch smallint = int 452
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 453
 QTYP 4 Comparison type mismatch smallint = int 453
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 454
 QTYP 4 Comparison type mismatch smallint = int 454
 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 123
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 137
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 139
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 157
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 164
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 185
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 187
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 208
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 210
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 233
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 235
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 258
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 260
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 283
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 285
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 308
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 310
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 333
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 335
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 358
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 360
 MAW1 3 Warning message on %name% sybsystemprocs..syskeys.id: Warning message on syskeys 399
 MAW1 3 Warning message on %name% sybsystemprocs..syskeys.id: Warning message on syskeys 433
 MAW1 3 Warning message on %name% sybsystemprocs..syskeys.id: Warning message on syskeys 473
 MAW1 3 Warning message on %name% sybsystemprocs..syskeys.id: Warning message on syskeys 490
 MGTP 3 Grant to public master..spt_values  
 MGTP 3 Grant to public sybsystemprocs..sp_commonkey  
 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 @@error after insert 473
 MNER 3 No Error Check should check @@error after insert 490
 MNER 3 No Error Check should check return value of exec 529
 MUCO 3 Useless Code Useless Brackets 100
 MUCO 3 Useless Code Useless Brackets 111
 MUCO 3 Useless Code Useless Brackets 135
 MUCO 3 Useless Code Useless Brackets 148
 MUCO 3 Useless Code Useless Brackets 169
 MUCO 3 Useless Code Useless Brackets 175
 MUCO 3 Useless Code Useless Brackets 199
 MUCO 3 Useless Code Useless Brackets 222
 MUCO 3 Useless Code Useless Brackets 247
 MUCO 3 Useless Code Useless Brackets 272
 MUCO 3 Useless Code Useless Brackets 297
 MUCO 3 Useless Code Useless Brackets 322
 MUCO 3 Useless Code Useless Brackets 347
 MUCO 3 Useless Code Useless Brackets 372
 MUCO 3 Useless Code Useless Brackets 396
 MUCO 3 Useless Code Useless Brackets 424
 MUCO 3 Useless Code Useless Brackets 458
 MUCO 3 Useless Code Useless Brackets 500
 MUCO 3 Useless Code Useless Brackets 503
 MUCO 3 Useless Code Useless Brackets 510
 MUCO 3 Useless Code Useless Brackets 521
 MUCO 3 Useless Code Useless Brackets 532
 QAFM 3 Var Assignment from potentially many rows 181
 QAFM 3 Var Assignment from potentially many rows 204
 QAFM 3 Var Assignment from potentially many rows 229
 QAFM 3 Var Assignment from potentially many rows 254
 QAFM 3 Var Assignment from potentially many rows 279
 QAFM 3 Var Assignment from potentially many rows 304
 QAFM 3 Var Assignment from potentially many rows 329
 QAFM 3 Var Assignment from potentially many rows 354
 QISO 3 Set isolation level 87
 QNAJ 3 Not using ANSI Inner Join 182
 QNAJ 3 Not using ANSI Inner Join 205
 QNAJ 3 Not using ANSI Inner Join 230
 QNAJ 3 Not using ANSI Inner Join 255
 QNAJ 3 Not using ANSI Inner Join 280
 QNAJ 3 Not using ANSI Inner Join 305
 QNAJ 3 Not using ANSI Inner Join 330
 QNAJ 3 Not using ANSI Inner Join 355
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
184
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
186
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
207
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
209
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
232
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
234
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
257
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
259
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
282
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
284
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
307
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
309
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
332
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
334
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
357
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
359
 QSWV 3 Sarg with variable @cnt, Candidate Index: syskeys.csyskeys clustered(id) S 402
 QSWV 3 Sarg with variable @key1a, Candidate Index: syskeys.csyskeys clustered(id) S 404
 QSWV 3 Sarg with variable @key2a, Candidate Index: syskeys.csyskeys clustered(id) S 405
 QSWV 3 Sarg with variable @key3a, Candidate Index: syskeys.csyskeys clustered(id) S 406
 QSWV 3 Sarg with variable @key4a, Candidate Index: syskeys.csyskeys clustered(id) S 407
 QSWV 3 Sarg with variable @key5a, Candidate Index: syskeys.csyskeys clustered(id) S 408
 QSWV 3 Sarg with variable @key6a, Candidate Index: syskeys.csyskeys clustered(id) S 409
 QSWV 3 Sarg with variable @key7a, Candidate Index: syskeys.csyskeys clustered(id) S 410
 QSWV 3 Sarg with variable @key8a, Candidate Index: syskeys.csyskeys clustered(id) S 411
 QSWV 3 Sarg with variable @key1b, Candidate Index: syskeys.csyskeys clustered(id) S 413
 QSWV 3 Sarg with variable @key2b, Candidate Index: syskeys.csyskeys clustered(id) S 414
 QSWV 3 Sarg with variable @key3b, Candidate Index: syskeys.csyskeys clustered(id) S 415
 QSWV 3 Sarg with variable @key4b, Candidate Index: syskeys.csyskeys clustered(id) S 416
 QSWV 3 Sarg with variable @key5b, Candidate Index: syskeys.csyskeys clustered(id) S 417
 QSWV 3 Sarg with variable @key6b, Candidate Index: syskeys.csyskeys clustered(id) S 418
 QSWV 3 Sarg with variable @key7b, Candidate Index: syskeys.csyskeys clustered(id) S 419
 QSWV 3 Sarg with variable @key8b, Candidate Index: syskeys.csyskeys clustered(id) S 420
 QSWV 3 Sarg with variable @cnt, Candidate Index: syskeys.csyskeys clustered(id) S 436
 QSWV 3 Sarg with variable @key1b, Candidate Index: syskeys.csyskeys clustered(id) S 438
 QSWV 3 Sarg with variable @key2b, Candidate Index: syskeys.csyskeys clustered(id) S 439
 QSWV 3 Sarg with variable @key3b, Candidate Index: syskeys.csyskeys clustered(id) S 440
 QSWV 3 Sarg with variable @key4b, Candidate Index: syskeys.csyskeys clustered(id) S 441
 QSWV 3 Sarg with variable @key5b, Candidate Index: syskeys.csyskeys clustered(id) S 442
 QSWV 3 Sarg with variable @key6b, Candidate Index: syskeys.csyskeys clustered(id) S 443
 QSWV 3 Sarg with variable @key7b, Candidate Index: syskeys.csyskeys clustered(id) S 444
 QSWV 3 Sarg with variable @key8b, Candidate Index: syskeys.csyskeys clustered(id) S 445
 QSWV 3 Sarg with variable @key1a, Candidate Index: syskeys.csyskeys clustered(id) S 447
 QSWV 3 Sarg with variable @key2a, Candidate Index: syskeys.csyskeys clustered(id) S 448
 QSWV 3 Sarg with variable @key3a, Candidate Index: syskeys.csyskeys clustered(id) S 449
 QSWV 3 Sarg with variable @key4a, Candidate Index: syskeys.csyskeys clustered(id) S 450
 QSWV 3 Sarg with variable @key5a, Candidate Index: syskeys.csyskeys clustered(id) S 451
 QSWV 3 Sarg with variable @key6a, Candidate Index: syskeys.csyskeys clustered(id) S 452
 QSWV 3 Sarg with variable @key7a, Candidate Index: syskeys.csyskeys clustered(id) S 453
 QSWV 3 Sarg with variable @key8a, Candidate Index: syskeys.csyskeys clustered(id) S 454
 MSUB 2 Subquery Marker 121
 MSUB 2 Subquery Marker 137
 MSUB 2 Subquery Marker 398
 MSUB 2 Subquery Marker 432
 MTR1 2 Metrics: Comments Ratio Comments: 38% 36
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 131 = 147dec - 18exi + 2 36
 MTR3 2 Metrics: Query Complexity Complexity: 313 36
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, c2=sybsystemprocs..syscolumns, sv=master..spt_values, sv2=master..spt_values} 0 181
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, c2=sybsystemprocs..syscolumns, sv=master..spt_values, sv2=master..spt_values} 0 204
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, c2=sybsystemprocs..syscolumns, sv=master..spt_values, sv2=master..spt_values} 0 229
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, c2=sybsystemprocs..syscolumns, sv=master..spt_values, sv2=master..spt_values} 0 254
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, c2=sybsystemprocs..syscolumns, sv=master..spt_values, sv2=master..spt_values} 0 279
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, c2=sybsystemprocs..syscolumns, sv=master..spt_values, sv2=master..spt_values} 0 304
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, c2=sybsystemprocs..syscolumns, sv=master..spt_values, sv2=master..spt_values} 0 329
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, c2=sybsystemprocs..syscolumns, sv=master..spt_values, sv2=master..spt_values} 0 354

DATA PROPAGATION detailed
ColumnWritten To
@tabanamesyskeys.id   °.depid  
@tabbnamesyskeys.id   °.depid  

DEPENDENCIES
PROCS AND TABLES USED
read_writes table sybsystemprocs..syskeys  
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