DatabaseProcApplicationCreatedLinks
sybsystemprocssp_depends_cols  31 Aug 14Defects Dependencies

1     
2     /*
3     ** Messages for "sp_depends_cols"
4     **
5     ** There are no messages for this sproc. Its an 'internal' sproc, and only
6     ** inserts output rows into a #temp table. The only message raised by this
7     ** sproc is a generic one as follows:
8     **
9     ** 17563, "The table does not have a column named '%1!'."
10    */
11    
12    /*
13    ** IMPORTANT NOTE:
14    ** This stored procedure uses the built-in function object_id() in the
15    ** where clause of a select query. If you intend to change this query
16    ** or use the object_id() or db_id() builtin in this procedure, please read the
17    ** READ.ME file in the $DBMS/generic/sproc directory to ensure that the rules
18    ** pertaining to object-id's and db-id's outlined there, are followed.
19    **
20    ** This is an internally used sproc called by sp_depends. The interface for
21    ** this is not documented, and cannot be directly invoked by the user.
22    */
23    
24    create procedure sp_depends_cols
25        @objname varchar(767) /* the object we want to check */
26        , @column_name varchar(255) = null /* the column we want to check */
27        , @findTableLevelConstraints int = 0 /* default is first call when user has
28    					** specified column name to sp_depends.
29    					** Otherwise, sp_depends will call us
30    					** iteratively for each column.
31    					*/
32    as
33    
34        declare @indid int
35            , @keycnt int /* # of keys in index */
36            , @colid int /* column id from syscolumns */
37            , @cdefault int /* default id from syscolumns */
38            , @domain int /* rule/check constraint from syscolumns */
39            , @accessrule int /* accessrule from syscolumns */
40            , @repstatus int /* status from syscolumns, to infer
41            ** replication status of column
42            */
43            , @colidarray varbinary(100) /* column id array from sysstatistics */
44            , @col_index tinyint /* index to parse colidarray 
45            ** from sysstatistics. That array is
46            ** 100 bytes, so tinyint will suffice.
47            */
48            , @statstring varchar(256) /* used for formatting column lists */
49            , @statcolumn varchar(255)
50            , @statcount tinyint
51            , @objid int
52            , @id int
53            , @type tinyint
54            , @depid int
55            , @frgndbname varchar(30)
56            , @constrid int
57            , @reftabid int
58            , @key1 int /* these are keys for checking syskeys */
59            , @key2 int
60            , @key3 int
61            , @key4 int
62            , @key5 int
63            , @key6 int
64            , @key7 int
65            , @key8 int
66            , @depkey1 int
67            , @depkey2 int
68            , @depkey3 int
69            , @depkey4 int
70            , @depkey5 int
71            , @depkey6 int
72            , @depkey7 int
73            , @depkey8 int
74    
75    
76        if @@trancount = 0
77        begin
78            set chained off
79        end
80    
81        set transaction isolation level 1
82        set nocount on
83    
84        /* 
85        ** Column name should have been provided. Check that it exists in the
86        ** said table. (Also used to initialize @cdefault, @domain @accessrule 
87        ** etc. below.)
88        */
89        if @column_name is not null
90        begin
91            select @objid = object_id(@objname)
92    
93            select @colid = colid, @cdefault = cdefault, @domain = domain
94                , @accessrule = accessrule, @repstatus = status
95            from syscolumns
96            where id = @objid and name = @column_name
97    
98            if (@colid is NULL)
99            begin
100               /*
101               ** 17563, "The table does not have a column named '%1!'."
102               */
103               raiserror 17563, @column_name
104               return (1)
105           end
106       end
107       else
108       begin
109           /*
110           ** This is an unexpected error. sp_depends will always provide
111           ** a column name. In any case, report a useful error.
112           */
113           raiserror 17563, @column_name
114           return (1)
115       end
116       /*
117       ** =========================================================================
118       ** Start of column level dependency processing.
119       ** =========================================================================
120       */
121   
122       declare @Depend_col_length int
123   
124       -- Length of Dependency column in output table
125       select @Depend_col_length = 255
126   
127       /* 
128       ** =========================================================================
129       ** Report replication status of column.
130       **
131       ** Note that @repnever, @repalways, @repifchanged are never translated
132       ** into local languages, so it is safe to declare them as varchar(30)
133       ** rather than extend them to varchar(1024).
134       */
135       declare @texttype int
136           , @imagetype int
137           , @javacoltype int
138           , @rep_constant smallint
139           , @offrowstat int
140           , @repnever varchar(30)
141           , @repalways varchar(30)
142           , @repifchanged varchar(30)
143   
144       select @texttype = 34, @imagetype = 35, @javacoltype = 36
145   
146           -- Replication enabled flag is 0x8000 (which is -32768D)
147           , @rep_constant = - 32768
148           , @offrowstat = 1
149   
150       /*
151       ** Initialize strings to specify replication status
152       */
153       exec sp_getmessage 18111, @repnever out -- "do_not_replicate"
154       exec sp_getmessage 18112, @repalways out -- "always_replicate"
155       exec sp_getmessage 18113, @repifchanged out -- "replicate_if_changed"
156   
157       insert into #column_depends
158       select case when c.type = @javacoltype then "Java column replication"
159               when c.type in (@texttype, @imagetype) then "Text/image replication"
160               else ""
161           end
162           , "replication"
163           , case (c.status & 3)
164               when 0 then @repnever
165               when 1 then @repalways
166               when 2 then @repifchanged
167               else " "
168           end
169           , c.name
170           , "sp_setrepcol, sp_setreptable"
171       from syscolumns c
172       where c.id = @objid
173   
174           and (select (sysstat & @rep_constant) -- replication status for table
175               from sysobjects
176               where id = @objid) != 0 -- => object is replicated (If object
177           -- is not replicated, do not output
178           -- any replication info.)
179           and c.colid = @colid
180           -- Only extract rows for text/image
181           -- columns, or off-row Java columns.
182           and (c.type in (@texttype, @imagetype)
183               or (c.type = @javacoltype and c.xstatus & @offrowstat = @offrowstat))
184           and (c.status & 3) in (0, 1, 2)
185   
186       /* 
187       ** =========================================================================
188       ** Report any indexes on the column.
189       ** Initialize @indid, and search the sysindexes column list for
190       ** all indexes on the table for the specified column. 
191       **
192       ** Since the status2 column in sysindexes means a foreign key
193       ** reference exists for the column, we'll handle those here too.
194       */
195       select @indid = 0
196   
197       while @indid is not null
198       begin -- { start of indid check
199           select @indid = min(indid)
200           from sysindexes
201           where id = object_id(@objname)
202               and indid > @indid
203               and indid < 255
204   
205           -- Account for the RID key in the keycnt for non-clust indexes.
206           -- (indid > 1 for non-clustered indexes)
207           --
208           select @keycnt = case when @indid > 1 then (keycnt - 1)
209                   else keycnt
210               end
211           from sysindexes
212           where id = object_id(@objname)
213               and indid = @indid
214   
215           /*
216           **  First we'll figure out what the index keys are.
217           */
218           declare @i int,
219               @found_depind int,
220               @status2 int, -- From sysindexes
221               @keyname varchar(255), -- Name of index key column
222               @indkeys_str varchar(256), -- List of index key columns
223               @found_fkref int -- Found an index which has
224           -- foreign key references from
225           -- another table.
226   
227           select @i = 1, @keyname = "start work", @indkeys_str = NULL
228               , @found_depind = 0
229   
230           while (@i <= @keycnt)
231           begin -- { start of "keys" while loop
232   
233               -- Find out the current key column's name.
234               select @keyname = index_col(@objname, @indid, @i)
235   
236               select @indkeys_str = @indkeys_str +
237                   case @indkeys_str
238                       when NULL then " (" + @keyname
239                       else ", " + @keyname
240                   end
241   
242               if (@keyname = @column_name)
243               begin -- { start of "if found column name"
244   
245                   select @found_depind = 1
246   
247                   /* 
248                   ** This detects foreign key references from other
249                   ** tables and prints the name of the foreign key
250                   ** constraint. Since very long DB + table + constraint
251                   ** names are longer than the #column_depends
252                   ** column size, use special handling for the lengths.
253                   **
254                   ** References to other tables are detected in another 
255                   ** section. 
256                   */
257                   if (select (status2 & 1) from sysindexes
258                           where id = object_id(@objname)
259                               and indid = @indid) = 1
260                   begin
261   
262                       -- Remember that we found an index defined
263                       -- as a foreign key reference. We'll use this
264                       -- boolean to scan sysreferences later on.
265                       --
266                       select @found_fkref = 1
267                   end
268               end -- } end of "if found column name"
269   
270               /*
271               **  Increment @i so it will check for the next index key column.
272               */
273               select @i = @i + 1
274   
275           end -- } end of "keys" while loop
276   
277           -- If we found a dependent index, output its info.
278           if (@found_depind = 1)
279           begin
280               -- Prefix index name to string of key names.
281               --
282               select @indkeys_str = name + @indkeys_str + ")"
283                   , @status2 = status2
284               from sysindexes
285               where id = object_id(@objname)
286                   and indid = @indid
287   
288               -- Populate result table w/info on index on this column
289               insert into #column_depends values
290               ("index"
291                   , case when (@status2 & 2 = 0) then "index"
292                       when (@status2 & 2 = 2) then "constraint"
293                   end
294                   , case
295                       when (datalength(@indkeys_str) >
296                               @Depend_col_length)
297   
298                       -- Truncate string and append with '...)'
299                       then convert(char(251), @indkeys_str) + "...)"
300                       else @indkeys_str
301                   end
302                   , @column_name
303                   , "sp_helpindex, drop index, sp_helpconstraint, alter table drop constraint"
304               )
305           end
306       end -- } end of indid check
307   
308       /*
309       ** =========================================================================
310       ** If we found even one index defined as a foreign key reference containing
311       ** the column of interest as an index key, then scan sysreferences to collect
312       ** names of the foreign key reference, and populate the output table.
313       */
314       if (@found_fkref = 1)
315       begin
316           declare @foreignkey_str varchar(255)
317   
318           -- See if this column appears as a foreign 
319           -- key column from sysreferences.
320           --
321           declare fkref cursor for
322           select frgndbname, constrid, tableid
323           from sysreferences
324           where reftabid = object_id(@objname)
325               and (refkey1 = @colid or
326                   refkey2 = @colid or
327                   refkey3 = @colid or
328                   refkey4 = @colid or
329                   refkey5 = @colid or
330                   refkey6 = @colid or
331                   refkey7 = @colid or
332                   refkey8 = @colid or
333                   refkey9 = @colid or
334                   refkey10 = @colid or
335                   refkey11 = @colid or
336                   refkey12 = @colid or
337                   refkey13 = @colid or
338                   refkey14 = @colid or
339                   refkey15 = @colid or
340                   refkey16 = @colid)
341   
342           open fkref
343           fetch fkref into @frgndbname, @constrid, @reftabid
344   
345           while (@@sqlstatus != 2)
346           begin -- { start of fkref cursor loop
347               select @foreignkey_str =
348                   case
349                       when (@frgndbname is NULL)
350                       then object_name(@constrid)
351                       + " on " + object_name(@reftabid)
352                       else @frgndbname
353                           + ".."
354                           + object_name(@reftabid, db_id(@frgndbname))
355                           + " "
356                           + object_name(@constrid, db_id(@frgndbname))
357                   end
358   
359               insert into #column_depends
360               select "foreign key reference"
361                   , "constraint"
362                   , @foreignkey_str
363                   , @column_name
364                   , "sp_helpconstraint, alter table drop constraint"
365   
366               fetch fkref into @frgndbname, @constrid, @reftabid
367           end -- } end of fkref cursor loop
368   
369           close fkref
370           deallocate cursor fkref
371       end
372   
373       /*
374       ** =========================================================================
375       ** Defaults:
376       **	Procedural	Created by sp_addefault, sp_bindefault.
377       **	Declarative	Created by specifying the default clause in the
378       **			CREATE/ALTER TABLE statements.
379       */
380       if (@cdefault != 0)
381       begin
382           insert into #column_depends
383           select "default"
384               , case when (pr.status & 4096 = 4096)
385                   then "declarative"
386                   else "procedural"
387               end
388               , object_name(col.cdefault)
389               , @column_name
390               , "sp_help, " + case when (pr.status & 4096 = 4096)
391                   then "alter table drop constraint"
392                   else "sp_unbindefault"
393               end
394           from syscolumns col, sysprocedures pr
395           where col.id = object_id(@objname)
396               and col.colid = @colid
397               and col.name = @column_name
398               and col.cdefault = pr.id
399               and pr.sequence = 1
400       end
401   
402       /* 
403       ** =========================================================================
404       ** Rules:
405       ** Get the rules, with ID's stored in syscolumns.domain, or 
406       ** syscolumns.accessrule.
407       ** Eliminate the check constraints, the next step locates them.
408       */
409       if (@domain != 0)
410       begin
411           insert into #column_depends
412           select "rule", "procedural", object_name(col.domain)
413               , @column_name
414               , "sp_help, sp_unbindrule"
415           from syscolumns col
416           where col.id = object_id(@objname)
417               and col.name = @column_name
418               and col.domain not in (select constrid from sysconstraints con
419                   where con.colid = @colid
420                       and con.tableid = object_id(@objname)
421                       and status = 128)
422       end
423   
424       if (isnull(@accessrule, 0) != 0)
425       begin
426           insert into #column_depends
427           select "accessrule", "procedural", object_name(col.accessrule)
428               , @column_name
429               , "sp_help, sp_unbindrule"
430           from syscolumns col
431           where col.id = object_id(@objname)
432               and col.name = @column_name
433       end
434   
435   
436       /* 
437       ** =========================================================================
438       ** Get the check constraints and referential constraints
439       ** by checking the status bits in sysconstraints
440       */
441   
442       /*
443       ** Table-level constraints are stored with colid=0. We need to present this
444       ** output to the reader in two ways.
445       **
446       ** 1. If user issues sp_depends on a single-column, then include all info
447       **    of constraints on the column of choice, and the info on table-level
448       **    check constraints. The check for this case is
449       **    if (@findTableLevelConstraints = 0 and colid = 0).
450       **
451       ** 2. If the user is running sp_depends for the table (i.e. to get output
452       **    for all columns in the table), then, the output for table-level
453       **    constraints is not done here. Its generated in the caller's context.
454       */
455       if exists (select colid from sysconstraints
456               where tableid = object_id(@objname)
457                   and colid = @colid)
458       begin
459           insert into #column_depends
460   
461           select
462               -- Form string like: 'table-level check constraint',
463               -- 'check constraint', 'referential constraint'.
464               --
465               case when (@findTableLevelConstraints = 0 and colid = 0)
466   
467                   -- check for table-level constraints
468                   then "table-level check"
469   
470                   else case when status & 128 = 128 then "check"
471                           when status & 64 = 64 then "referential"
472                       end
473               end
474               + " constraint"
475   
476               , "constraint"
477               , object_name(constrid)
478               , case when (@findTableLevelConstraints = 0 and colid = 0)
479                   then "(Unknown column name)"
480                   else @column_name
481               end
482   
483               , "sp_helpconstraint, alter table drop constraint"
484   
485           from sysconstraints
486           where tableid = object_id(@objname)
487               and (colid = @colid -- For this column
488   
489                   -- For table-level constraints during the very first call.
490                   or (@findTableLevelConstraints = 0 and colid = 0))
491       end
492   
493       /*
494       ** =========================================================================
495       ** sysstatistics:
496       ** Get all of the column-name groups from sysstatistics
497       ** that include this column
498       */
499       declare statrows cursor for
500       select colidarray
501       from sysstatistics
502       where id = object_id(@objname)
503           and formatid = 100 -- column statistics rows
504           and statid = 0
505   
506       declare @statrowqualifies int
507           , @colid_size int -- in bytes
508   
509       -- sysstatistics.colidarray is 2 bytes each. Setting this to 2 will
510       -- ensure that we can use sp_depends after upgrade has completed. But,
511       -- if it is used before upgrade has completed, we will get incorrect
512       -- info as sysstatistics would not have been upgraded, yet. That is a
513       -- small window to worry about.
514       --
515       select @colid_size = 2
516   
517       open statrows
518   
519       fetch statrows into @colidarray
520       while (@@sqlstatus != 2)
521       begin -- {
522   
523           select @col_index = 1, @statcount = 0, @statrowqualifies = 0
524   
525           -- Parse colidarray, searching for match on required colid.
526           -- (Use <= rather than < as varbinary colidarray will have trailing
527           --  0s truncated. On NT/Linux, if we used <, we would miss out on
528           -- the last column in this array. Same reason below.)
529           --
530           while (@col_index <= datalength(@colidarray))
531           begin
532               if (select convert(smallint, substring(@colidarray, @col_index,
533                           @colid_size))) = @colid
534               begin
535                   select @statrowqualifies = 1
536                   break
537               end
538               else
539                   select @col_index = @col_index + @colid_size
540           end
541   
542           -- If row qualifies, re-parse the columns it contains, and build
543           -- a string of column names. Output that to the #temp table.
544           --
545           if (@statrowqualifies = 1)
546           begin -- {
547   
548               select @col_index = 1, @statstring = NULL
549   
550               -- (See note above why we use <= rather than <)
551               while (@col_index <= datalength(@colidarray))
552               begin -- {
553   
554                   select @statcolumn =
555                       col_name(object_id(@objname),
556                           convert(smallint,
557                           substring(@colidarray,
558                               @col_index,
559                               @colid_size)))
560   
561                   -- Concatenate, accounting for initial empty string.
562                   --
563                   select @statstring = @statstring
564                       + case when (@statstring IS NULL)
565                           then "(" + @statcolumn
566                           else ", " + @statcolumn
567                       end
568   
569                   select @col_index = @col_index + @colid_size
570   
571               end -- }
572   
573               select @statstring = @statstring + ")"
574   
575               insert into #column_depends values
576               ("statistics", "row"
577                   , case
578                       when (datalength(@statstring) >
579                               @Depend_col_length)
580   
581                       -- Truncate string and append with '...)'
582                       then convert(char(251), @statstring) + "...)"
583                       else @statstring
584                   end
585                   , @column_name, "update statistics")
586           end -- }
587   
588           fetch statrows into @colidarray
589   
590       end -- }
591   
592       close statrows
593       deallocate cursor statrows
594   
595       /*
596       ** =========================================================================
597       ** Check for permissions on the column.
598       ** Note that grant select on mytab...revoke select on mytab(c1)...
599       ** means that sysprotects has column perms grants for all OTHER
600       ** columns of mytab.
601       */
602       if exists (select * from sysprotects p, master..spt_values c
603               where c.type = "P" and c.number <= 1024
604                   and id = object_id(@objname)
605                   and convert(tinyint,
606                   substring(isnull(p.columns, 0x1),
607                       c.low, 1)) & c.high != 0
608                   and col_name(id, number) = @column_name)
609       begin
610           insert into #column_depends values
611           ("permission", "permission", "column permission"
612               , @column_name, "sp_helprotect, grant/revoke")
613       end
614   
615       /* 
616       ** =========================================================================
617       ** Look in syskeys for any primary, foreign or common keys 
618       */
619       if exists (select * from syskeys
620               where (id = object_id(@objname)
621                       and (key1 = @colid or
622                           key2 = @colid or
623                           key3 = @colid or
624                           key4 = @colid or
625                           key5 = @colid or
626                           key6 = @colid or
627                           key7 = @colid or
628                           key8 = @colid))
629   
630                   or (depid = object_id(@objname)
631                       and (depkey1 = @colid or
632                           depkey2 = @colid or
633                           depkey3 = @colid or
634                           depkey4 = @colid or
635                           depkey5 = @colid or
636                           depkey6 = @colid or
637                           depkey7 = @colid or
638                           depkey8 = @colid))
639               )
640       begin
641           -- Strings to store comma separated concatenated string of names
642           -- of key columns for Primary->Foreign tables.
643           --
644           declare @syskeys_str varchar(255)
645           declare @depkeys_str varchar(255)
646   
647           declare lrikeys cursor for
648           select id, type, depid
649               , key1, key2, key3, key4, key5, key6, key7, key8
650               , depkey1, depkey2, depkey3, depkey4, depkey5, depkey6
651               , depkey7, depkey8
652           from syskeys
653           where (id = object_id(@objname)
654                   and (key1 = @colid or -- Column is a primary key
655                       key2 = @colid or -- column.
656                       key3 = @colid or
657                       key4 = @colid or
658                       key5 = @colid or
659                       key6 = @colid or
660                       key7 = @colid or
661                       key8 = @colid))
662   
663               or (depid = object_id(@objname)
664                   and (depkey1 = @colid or -- Column is a foreign key
665                       depkey2 = @colid or -- column.
666                       depkey3 = @colid or
667                       depkey4 = @colid or
668                       depkey5 = @colid or
669                       depkey6 = @colid or
670                       depkey7 = @colid or
671                       depkey8 = @colid))
672   
673           open lrikeys
674           fetch lrikeys into @id, @type, @depid
675               , @key1, @key2, @key3, @key4, @key5, @key6, @key7, @key8
676               , @depkey1, @depkey2, @depkey3, @depkey4, @depkey5, @depkey6
677               , @depkey7, @depkey8
678   
679           while (@@sqlstatus != 2)
680           begin
681               -- Build the primary table's key column names string
682               -- using (@id, @key)
683               --
684               select @syskeys_str =
685                   " ("
686                   + case when @key1 is not null
687                       then col_name(@id, @key1)
688                   end
689                   + case when @key2 is not null
690                       then ", " + col_name(@id, @key2)
691                   end
692                   + case when @key3 is not null
693                       then ", " + col_name(@id, @key3)
694                   end
695                   + case when @key4 is not null
696                       then ", " + col_name(@id, @key4)
697                   end
698                   + case when @key5 is not null
699                       then ", " + col_name(@id, @key5)
700                   end
701                   + case when @key6 is not null
702                       then ", " + col_name(@id, @key6)
703                   end
704                   + case when @key7 is not null
705                       then ", " + col_name(@id, @key7)
706                   end
707                   + case when @key8 is not null
708                       then ", " + col_name(@id, @key8)
709                   end
710                   + ")"
711   
712               -- Build the foreign table's key column names string
713               -- using (@depid, @depkey)
714               --
715               if (@type = 2) or (@type = 3)
716               begin
717   
718                   select @depkeys_str =
719                       " ("
720                       + case when @depkey1 is not null
721                           then col_name(@depid, @depkey1)
722                       end
723                       + case when @depkey2 is not null
724                           then ", " + col_name(@depid, @depkey2)
725                       end
726                       + case when @depkey3 is not null
727                           then ", " + col_name(@depid, @depkey3)
728                       end
729                       + case when @depkey4 is not null
730                           then ", " + col_name(@depid, @depkey4)
731                       end
732                       + case when @depkey5 is not null
733                           then ", " + col_name(@depid, @depkey5)
734                       end
735                       + case when @depkey6 is not null
736                           then ", " + col_name(@depid, @depkey6)
737                       end
738                       + case when @depkey7 is not null
739                           then ", " + col_name(@depid, @depkey7)
740                       end
741                       + case when @depkey8 is not null
742                           then ", " + col_name(@depid, @depkey8)
743                       end
744                       + ")"
745               end
746   
747               if (@type = 1)
748                   insert into #column_depends values
749                   ("logical RI", "primary", @objname + @syskeys_str
750                       , @column_name, "sp_helpkey, sp_dropkey")
751   
752               if (@type = 2) or (@type = 3)
753                   insert into #column_depends values
754                   ("logical RI"
755   
756                       , case when @type = 2 then "foreign"
757                           else "common"
758                       end
759                       , "From " + object_name(@id) + @syskeys_str +
760                       " To " + object_name(@depid) + @depkeys_str
761                       , @column_name, "sp_helpkey, sp_dropkey")
762   
763               fetch lrikeys into @id, @type, @depid,
764                   @key1, @key2, @key3, @key4, @key5, @key6, @key7, @key8
765                   , @depkey1, @depkey2, @depkey3, @depkey4, @depkey5
766                   , @depkey6, @depkey7, @depkey8
767           end
768       end
769   
770       set nocount off
771       return (0)
772   


exec sp_procxmode 'sp_depends_cols', 'AnyMode'
go

Grant Execute on sp_depends_cols to public
go
DEFECTS
 QJWI 5 Join or Sarg Without Index 418
 QJWI 5 Join or Sarg Without Index 605
 MEST 4 Empty String will be replaced by Single Space 160
 MINU 4 Unique Index with nullable columns sybsystemprocs..sysprotects sybsystemprocs..sysprotects
 MINU 4 Unique Index with nullable columns sybsystemprocs..sysstatistics sybsystemprocs..sysstatistics
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 158
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 179
 QTYP 4 Comparison type mismatch smallint = int 179
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 183
 QTYP 4 Comparison type mismatch tinyint = int 183
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 202
 QTYP 4 Comparison type mismatch smallint = int 202
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 203
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 213
 QTYP 4 Comparison type mismatch smallint = int 213
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 259
 QTYP 4 Comparison type mismatch smallint = int 259
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 286
 QTYP 4 Comparison type mismatch smallint = int 286
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 325
 QTYP 4 Comparison type mismatch smallint = int 325
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 326
 QTYP 4 Comparison type mismatch smallint = int 326
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 327
 QTYP 4 Comparison type mismatch smallint = int 327
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 328
 QTYP 4 Comparison type mismatch smallint = int 328
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 329
 QTYP 4 Comparison type mismatch smallint = int 329
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 330
 QTYP 4 Comparison type mismatch smallint = int 330
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 331
 QTYP 4 Comparison type mismatch smallint = int 331
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 332
 QTYP 4 Comparison type mismatch smallint = int 332
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 333
 QTYP 4 Comparison type mismatch smallint = int 333
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 334
 QTYP 4 Comparison type mismatch smallint = int 334
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 335
 QTYP 4 Comparison type mismatch smallint = int 335
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 336
 QTYP 4 Comparison type mismatch smallint = int 336
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 337
 QTYP 4 Comparison type mismatch smallint = int 337
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 338
 QTYP 4 Comparison type mismatch smallint = int 338
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 339
 QTYP 4 Comparison type mismatch smallint = int 339
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 340
 QTYP 4 Comparison type mismatch smallint = int 340
 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 419
 QTYP 4 Comparison type mismatch smallint = int 419
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 457
 QTYP 4 Comparison type mismatch smallint = int 457
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 465
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 478
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 487
 QTYP 4 Comparison type mismatch smallint = int 487
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 490
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 503
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 504
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 621
 QTYP 4 Comparison type mismatch smallint = int 621
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 622
 QTYP 4 Comparison type mismatch smallint = int 622
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 623
 QTYP 4 Comparison type mismatch smallint = int 623
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 624
 QTYP 4 Comparison type mismatch smallint = int 624
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 625
 QTYP 4 Comparison type mismatch smallint = int 625
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 626
 QTYP 4 Comparison type mismatch smallint = int 626
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 627
 QTYP 4 Comparison type mismatch smallint = int 627
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 628
 QTYP 4 Comparison type mismatch smallint = int 628
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 631
 QTYP 4 Comparison type mismatch smallint = int 631
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 632
 QTYP 4 Comparison type mismatch smallint = int 632
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 633
 QTYP 4 Comparison type mismatch smallint = int 633
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 634
 QTYP 4 Comparison type mismatch smallint = int 634
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 635
 QTYP 4 Comparison type mismatch smallint = int 635
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 636
 QTYP 4 Comparison type mismatch smallint = int 636
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 637
 QTYP 4 Comparison type mismatch smallint = int 637
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 638
 QTYP 4 Comparison type mismatch smallint = int 638
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 654
 QTYP 4 Comparison type mismatch smallint = int 654
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 655
 QTYP 4 Comparison type mismatch smallint = int 655
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 656
 QTYP 4 Comparison type mismatch smallint = int 656
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 657
 QTYP 4 Comparison type mismatch smallint = int 657
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 658
 QTYP 4 Comparison type mismatch smallint = int 658
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 659
 QTYP 4 Comparison type mismatch smallint = int 659
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 660
 QTYP 4 Comparison type mismatch smallint = int 660
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 661
 QTYP 4 Comparison type mismatch smallint = int 661
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 664
 QTYP 4 Comparison type mismatch smallint = int 664
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 665
 QTYP 4 Comparison type mismatch smallint = int 665
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 666
 QTYP 4 Comparison type mismatch smallint = int 666
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 667
 QTYP 4 Comparison type mismatch smallint = int 667
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 668
 QTYP 4 Comparison type mismatch smallint = int 668
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 669
 QTYP 4 Comparison type mismatch smallint = int 669
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 670
 QTYP 4 Comparison type mismatch smallint = int 670
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 671
 QTYP 4 Comparison type mismatch smallint = int 671
 TNOU 4 Table with no unique index master..spt_values master..spt_values
 TNOU 4 Table with no unique index sybsystemprocs..syskeys sybsystemprocs..syskeys
 TNOU 4 Table with no unique index sybsystemprocs..sysreferences sybsystemprocs..sysreferences
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause fkref 322
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause statrows 500
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 606
 MGTP 3 Grant to public master..spt_values  
 MGTP 3 Grant to public sybsystemprocs..sp_depends_cols  
 MGTP 3 Grant to public sybsystemprocs..syscolumns  
 MGTP 3 Grant to public sybsystemprocs..sysconstraints  
 MGTP 3 Grant to public sybsystemprocs..sysindexes  
 MGTP 3 Grant to public sybsystemprocs..syskeys  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MGTP 3 Grant to public sybsystemprocs..sysprocedures  
 MGTP 3 Grant to public sybsystemprocs..sysprotects  
 MGTP 3 Grant to public sybsystemprocs..sysreferences  
 MGTP 3 Grant to public sybsystemprocs..sysstatistics  
 MLCH 3 Char type with length>30 char(251) 299
 MLCH 3 Char type with length>30 char(251) 582
 MNER 3 No Error Check should check return value of exec 153
 MNER 3 No Error Check should check return value of exec 154
 MNER 3 No Error Check should check return value of exec 155
 MNER 3 No Error Check should check @@error after insert 157
 MNER 3 No Error Check should check @@error after insert 289
 MNER 3 No Error Check should check @@error after insert 359
 MNER 3 No Error Check should check @@error after insert 382
 MNER 3 No Error Check should check @@error after insert 411
 MNER 3 No Error Check should check @@error after insert 426
 MNER 3 No Error Check should check @@error after insert 459
 MNER 3 No Error Check should check @@error after insert 575
 MNER 3 No Error Check should check @@error after insert 610
 MNER 3 No Error Check should check @@error after insert 748
 MNER 3 No Error Check should check @@error after insert 753
 MUCO 3 Useless Code Useless Brackets 98
 MUCO 3 Useless Code Useless Brackets 104
 MUCO 3 Useless Code Useless Brackets 114
 MUCO 3 Useless Code Useless Brackets 230
 MUCO 3 Useless Code Useless Brackets 242
 MUCO 3 Useless Code Useless Brackets 278
 MUCO 3 Useless Code Useless Brackets 291
 MUCO 3 Useless Code Useless Brackets 292
 MUCO 3 Useless Code Useless Brackets 295
 MUCO 3 Useless Code Useless Brackets 314
 MUCO 3 Useless Code Useless Brackets 345
 MUCO 3 Useless Code Useless Brackets 349
 MUCO 3 Useless Code Useless Brackets 380
 MUCO 3 Useless Code Useless Brackets 384
 MUCO 3 Useless Code Useless Brackets 390
 MUCO 3 Useless Code Useless Brackets 409
 MUCO 3 Useless Code Useless Brackets 424
 MUCO 3 Useless Code Useless Brackets 465
 MUCO 3 Useless Code Useless Brackets 478
 MUCO 3 Useless Code Useless Brackets 520
 MUCO 3 Useless Code Useless Brackets 530
 MUCO 3 Useless Code Useless Brackets 545
 MUCO 3 Useless Code Useless Brackets 551
 MUCO 3 Useless Code Useless Brackets 564
 MUCO 3 Useless Code Useless Brackets 578
 MUCO 3 Useless Code Useless Brackets 679
 MUCO 3 Useless Code Useless Brackets 747
 MUCO 3 Useless Code Useless Brackets 771
 QAFM 3 Var Assignment from potentially many rows 93
 QISO 3 Set isolation level 81
 QNAJ 3 Not using ANSI Inner Join 394
 QNAJ 3 Not using ANSI Inner Join 602
 QNUA 3 Should use Alias: Column id should use alias p 604
 QNUA 3 Should use Alias: Column id should use alias p 608
 QNUA 3 Should use Alias: Column number should use alias c 608
 QPNC 3 No column in condition 174
 QPNC 3 No column in condition 490
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
96
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {colid, id}
172
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysreferences.nc2sysreferences
(reftabid, indexid, pmrydbname)
Intersection: {reftabid}
324
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {colid, id}
395
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: sysprocedures.csysprocedures unique clustered
(id, number, type, sequence)
Intersection: {id}
Uncovered: [number, type]
398
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
416
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
431
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysstatistics.csysstatistics unique clustered
(id, indid, partitionid, statid, colidarray, formatid, sequence)
Intersection: {statid, id, formatid}
502
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysprotects.csysprotects unique clustered
(id, action, grantor, uid, protecttype)
Intersection: {id}
604
 QSWV 3 Sarg with variable @colid, Candidate Index: sysreferences.nc2sysreferences(reftabid, indexid, pmrydbname) P 325
 QSWV 3 Sarg with variable @colid, Candidate Index: sysreferences.nc2sysreferences(reftabid, indexid, pmrydbname) P 326
 QSWV 3 Sarg with variable @colid, Candidate Index: sysreferences.nc2sysreferences(reftabid, indexid, pmrydbname) P 327
 QSWV 3 Sarg with variable @colid, Candidate Index: sysreferences.nc2sysreferences(reftabid, indexid, pmrydbname) P 328
 QSWV 3 Sarg with variable @colid, Candidate Index: sysreferences.nc2sysreferences(reftabid, indexid, pmrydbname) P 329
 QSWV 3 Sarg with variable @colid, Candidate Index: sysreferences.nc2sysreferences(reftabid, indexid, pmrydbname) P 330
 QSWV 3 Sarg with variable @colid, Candidate Index: sysreferences.nc2sysreferences(reftabid, indexid, pmrydbname) P 331
 QSWV 3 Sarg with variable @colid, Candidate Index: sysreferences.nc2sysreferences(reftabid, indexid, pmrydbname) P 332
 QSWV 3 Sarg with variable @colid, Candidate Index: sysreferences.nc2sysreferences(reftabid, indexid, pmrydbname) P 333
 QSWV 3 Sarg with variable @colid, Candidate Index: sysreferences.nc2sysreferences(reftabid, indexid, pmrydbname) P 334
 QSWV 3 Sarg with variable @colid, Candidate Index: sysreferences.nc2sysreferences(reftabid, indexid, pmrydbname) P 335
 QSWV 3 Sarg with variable @colid, Candidate Index: sysreferences.nc2sysreferences(reftabid, indexid, pmrydbname) P 336
 QSWV 3 Sarg with variable @colid, Candidate Index: sysreferences.nc2sysreferences(reftabid, indexid, pmrydbname) P 337
 QSWV 3 Sarg with variable @colid, Candidate Index: sysreferences.nc2sysreferences(reftabid, indexid, pmrydbname) P 338
 QSWV 3 Sarg with variable @colid, Candidate Index: sysreferences.nc2sysreferences(reftabid, indexid, pmrydbname) P 339
 QSWV 3 Sarg with variable @colid, Candidate Index: sysreferences.nc2sysreferences(reftabid, indexid, pmrydbname) P 340
 QSWV 3 Sarg with variable @colid, Candidate Index: sysconstraints.csysconstraints clustered(tableid, colid) S 419
 QSWV 3 Sarg with variable @colid, Candidate Index: sysconstraints.csysconstraints clustered(tableid, colid) F 457
 QSWV 3 Sarg with variable @colid, Candidate Index: sysconstraints.csysconstraints clustered(tableid, colid) S 487
 QSWV 3 Sarg with variable @colid, Candidate Index: syskeys.csyskeys clustered(id) S 621
 QSWV 3 Sarg with variable @colid, Candidate Index: syskeys.csyskeys clustered(id) S 622
 QSWV 3 Sarg with variable @colid, Candidate Index: syskeys.csyskeys clustered(id) S 623
 QSWV 3 Sarg with variable @colid, Candidate Index: syskeys.csyskeys clustered(id) S 624
 QSWV 3 Sarg with variable @colid, Candidate Index: syskeys.csyskeys clustered(id) S 625
 QSWV 3 Sarg with variable @colid, Candidate Index: syskeys.csyskeys clustered(id) S 626
 QSWV 3 Sarg with variable @colid, Candidate Index: syskeys.csyskeys clustered(id) S 627
 QSWV 3 Sarg with variable @colid, Candidate Index: syskeys.csyskeys clustered(id) S 628
 QSWV 3 Sarg with variable @colid, Candidate Index: syskeys.csyskeys clustered(id) S 631
 QSWV 3 Sarg with variable @colid, Candidate Index: syskeys.csyskeys clustered(id) S 632
 QSWV 3 Sarg with variable @colid, Candidate Index: syskeys.csyskeys clustered(id) S 633
 QSWV 3 Sarg with variable @colid, Candidate Index: syskeys.csyskeys clustered(id) S 634
 QSWV 3 Sarg with variable @colid, Candidate Index: syskeys.csyskeys clustered(id) S 635
 QSWV 3 Sarg with variable @colid, Candidate Index: syskeys.csyskeys clustered(id) S 636
 QSWV 3 Sarg with variable @colid, Candidate Index: syskeys.csyskeys clustered(id) S 637
 QSWV 3 Sarg with variable @colid, Candidate Index: syskeys.csyskeys clustered(id) S 638
 QSWV 3 Sarg with variable @colid, Candidate Index: syskeys.csyskeys clustered(id) S 654
 QSWV 3 Sarg with variable @colid, Candidate Index: syskeys.csyskeys clustered(id) S 655
 QSWV 3 Sarg with variable @colid, Candidate Index: syskeys.csyskeys clustered(id) S 656
 QSWV 3 Sarg with variable @colid, Candidate Index: syskeys.csyskeys clustered(id) S 657
 QSWV 3 Sarg with variable @colid, Candidate Index: syskeys.csyskeys clustered(id) S 658
 QSWV 3 Sarg with variable @colid, Candidate Index: syskeys.csyskeys clustered(id) S 659
 QSWV 3 Sarg with variable @colid, Candidate Index: syskeys.csyskeys clustered(id) S 660
 QSWV 3 Sarg with variable @colid, Candidate Index: syskeys.csyskeys clustered(id) S 661
 QSWV 3 Sarg with variable @colid, Candidate Index: syskeys.csyskeys clustered(id) S 664
 QSWV 3 Sarg with variable @colid, Candidate Index: syskeys.csyskeys clustered(id) S 665
 QSWV 3 Sarg with variable @colid, Candidate Index: syskeys.csyskeys clustered(id) S 666
 QSWV 3 Sarg with variable @colid, Candidate Index: syskeys.csyskeys clustered(id) S 667
 QSWV 3 Sarg with variable @colid, Candidate Index: syskeys.csyskeys clustered(id) S 668
 QSWV 3 Sarg with variable @colid, Candidate Index: syskeys.csyskeys clustered(id) S 669
 QSWV 3 Sarg with variable @colid, Candidate Index: syskeys.csyskeys clustered(id) S 670
 QSWV 3 Sarg with variable @colid, Candidate Index: syskeys.csyskeys clustered(id) S 671
 QTLO 3 Top-Level OR 620
 QTLO 3 Top-Level OR 653
 VNRD 3 Variable is not read @repstatus 94
 CUPD 2 Updatable Cursor Marker (updatable by default) 322
 CUPD 2 Updatable Cursor Marker (updatable by default) 500
 CUPD 2 Updatable Cursor Marker (updatable by default) 648
 MSUB 2 Subquery Marker 174
 MSUB 2 Subquery Marker 257
 MSUB 2 Subquery Marker 418
 MSUB 2 Subquery Marker 455
 MSUB 2 Subquery Marker 602
 MSUB 2 Subquery Marker 619
 MTR1 2 Metrics: Comments Ratio Comments: 37% 24
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 89 = 90dec - 3exi + 2 24
 MTR3 2 Metrics: Query Complexity Complexity: 291 24
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, p=sybsystemprocs..sysprocedures} 0 383

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..sysreferences  
reads table sybsystemprocs..sysstatistics  
reads table sybsystemprocs..syscolumns  
reads table sybsystemprocs..sysconstraints  
reads table sybsystemprocs..sysobjects  
reads table sybsystemprocs..sysprotects  
reads table master..spt_values (1)  
reads table sybsystemprocs..syskeys  
reads table sybsystemprocs..sysindexes  
calls proc sybsystemprocs..sp_getmessage  
   reads table master..sysmessages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table sybsystemprocs..sysusermessages  
   reads table master..syslanguages (1)  
writes table tempdb..#column_depends (1) 
reads table sybsystemprocs..sysprocedures  

CALLERS
called by proc sybsystemprocs..sp_depends