DatabaseProcApplicationCreatedLinks
sybsystemprocssp_merge_dup_inline_default  14 déc. 14Defects Propagation Dependencies

1     
2     /*
3     ** Messages for "sp_merge_dup_inline_default"
4     **
5     ** 17260, "Can't run %1! from within a transaction." 
6     ** 17005, "The databases master, model, tempdb, sybsecurity, sybsystemprocs and mounted_sybsystemprocs cannot carry out this operation."
7     ** 17793, "System Administrator (SA) must set database '%1!' to single-user mode with sp_dboption before using '%2!'."
8     ** 17006, "Database is modified and in single-user mode."
9     ** 17795, "System Administrator (SA) must reset it to multi-user mode with sp_dboption."
10    */
11    
12    
13    
14    create or replace procedure sp_merge_dup_inline_default
15        @report_only varchar(30) = "YES",
16        @show_progress varchar(30) = "NO"
17    as
18    
19        declare @dbname varchar(30) /* holds database name */
20        declare @bitdesc varchar(30) /* bit description for the db */
21        declare @msg varchar(1024)
22        declare @rollback int
23        declare @user_id int
24        declare @def_val varchar(255)
25        declare @def_id int
26        declare @def_name varchar(255)
27        declare @new_def_text varchar(300)
28        declare @tot_def_removed int
29        declare @tot_sharable_converted int
30        declare @unique_def int
31        declare @tot_def int
32        declare @cur_prog int
33        declare @prog_milestone int
34        declare @prog_secondary_milestone int
35        declare @dot_per_milestone int
36        declare @num_of_milestones int
37        declare @num_of_secondary_milestones int
38        declare @nullarg char(1)
39        declare @dummy int
40        declare @status int
41        declare @gp_enabled int
42    
43    
44        /*
45        **  If we're in a transaction, disallow this 
46        */
47        if @@trancount > 0
48        begin
49            /*
50            ** 17260, "Can't run %1! from within a transaction." 
51            */
52            raiserror 17260, "sp_remoev_dup_inline_default"
53            return (1)
54        end
55        else
56        begin
57            set chained off
58        end
59    
60        set transaction isolation level 1
61    
62        set nocount on
63        set flushmessage on
64    
65        /*
66        **  Get the database name we are in:
67        */
68        select @dbname = db_name()
69    
70        /* 
71        ** If granular permissions is not enabled then sa_role is required.
72        ** If granular permissions is enabled then the permission 'manage database' is
73        ** required.  proc_role and proc_auditperm will also do auditing
74        ** if required. Both will also print error message if required.
75        */
76    
77        select @nullarg = NULL
78        execute @status = sp_aux_checkroleperm "sa_role", "manage database",
79            @dbname, @gp_enabled output
80        /* For Auditing */
81        if (@gp_enabled = 0)
82        begin
83            if (proc_role("sa_role") = 0)
84                return (1)
85        end
86        else
87        begin
88            select @dummy = proc_auditperm("manage database", @status, @dbname)
89        end
90    
91        if (@status != 0)
92            return (1)
93    
94        /*
95        **  Don't allow the names of master, tempdb, and model to be changed.
96        */
97        if @dbname in ("master", "model", "tempdb", "sybsecurity", "sybsystemprocs", "mounted_sybsystemprocs")
98        begin
99            /*
100           ** 17005, "The databases master, model, tempdb, sybsecurity, 
101           ** sybsystemprocs and mounted_sybsystemprocs cannot carry out this operation."
102           */
103           raiserror 17005
104           return (1)
105       end
106   
107       /* 
108       ** Check single user bit (4096) 
109       ** Database must be in single user mode to necessitate the removal of
110       ** existing default objects which could be more likely inuse otherwise.
111       */
112       select @bitdesc = null
113       select @bitdesc = v.name
114       from master.dbo.spt_values v, master.dbo.sysdatabases d
115       where d.dbid = db_id(@dbname)
116           and v.type = "D"
117           and d.status & v.number = 4096
118       if @bitdesc is null
119       begin
120           /*
121           ** 17793, "System Administrator (SA) must set database '%1!' to single-user mode with sp_dboption before using '%2!'."
122           */
123           raiserror 17793, @dbname, "sp_merge_dup_inline_default"
124           return (1)
125       end
126   
127       create table #defval_tab(uid int not null, inline_default varchar(255) null, dup_cnt int)
128       create unique index i1 on #defval_tab(uid, inline_default)
129       create table #dupdef_tab(id int)
130       create unique index i2 on #dupdef_tab(id)
131   
132       IF @show_progress = 'YES'
133       BEGIN
134           PRINT 'Calculating...'
135       END
136   
137       /*
138       ** locate all unique literal constant inline defaults.
139       */
140       INSERT INTO #defval_tab(uid, inline_default, dup_cnt)
141       SELECT uid, inline_default, count(*) dup_cnt
142       FROM (SELECT O.uid,
143               rtrim(case when right (D.text, 1) = char(10)
144                       then left (D.text, len(D.text) - 1)
145                       else D.text
146                   end) inline_default
147           FROM dbo.syscomments D,
148               dbo.sysobjects O,
149               dbo.sysprocedures P
150           WHERE D.id = O.id
151               and O.id = P.id
152               and O.type = 'D'
153               and P.type = 2
154               and P.sequence = 0
155               and P.number = 0
156               and P.status & 4096 = 4096
157               and D.number = 0
158               and D.colid = 1
159               and D.colid2 = 0
160               and D.texttype = 0
161               and (SELECT count(*)
162                   FROM dbo.syscomments DD
163                   WHERE DD.id = O.id) <= 1
164           ) T(uid, inline_default)
165       GROUP BY uid, inline_default
166       PLAN "(insert (group_hashing 
167   (nested (nl_join (nl_join 
168   (t_scan (table (O [dbo.sysobjects])))
169   (i_scan csyscomments (table (D [dbo.syscomments]))))
170   (i_scan csysprocedures (table (P [dbo.sysprocedures]))))
171   (subq (scalar_agg (i_scan csyscomments (table (DD [dbo.syscomments]))))))))"
172   
173       DELETE #defval_tab
174       WHERE inline_default is null
175   
176       /*
177       ** The default value must be a literal constant in the form
178       ** of '[^']*', N'[^']*', "[^"]*", N"[^"]", {[+-]}[0-9]*[.][0-9]*,
179       ** in other words, all strings without escaped string delimiters
180       ** in-between and all numbers. The sharable inline default
181       ** feature actually allows more forms of literal constant such
182       ** as scientic number representation. But I'm here making this
183       ** SP simpler and will ignore those cases for now.
184       */
185       DELETE #defval_tab
186       WHERE not ((substring(inline_default, 10, 1) = "'"
187                   AND
188                   right (inline_default, 1) = "'"
189                   AND
190                   charindex("'", substring(inline_default, 11, len(inline_default) - 11)) = 0)
191               OR
192               (substring(inline_default, 10, 2) = "N'"
193                   AND
194                   right (inline_default, 1) = "'"
195                   AND
196                   charindex("'", substring(inline_default, 12, len(inline_default) - 12)) = 0)
197               OR
198               (substring(inline_default, 10, 1) = '"'
199                   AND
200                   right (inline_default, 1) = '"'
201                   AND
202                   charindex('"', substring(inline_default, 11, len(inline_default) - 11)) = 0)
203               OR
204               (substring(inline_default, 10, 2) = 'N"'
205                   AND
206                   right (inline_default, 1) = '"'
207                   AND
208                   charindex('"', substring(inline_default, 12, len(inline_default) - 12)) = 0)
209               OR
210               (str_replace(
211                       str_replace(
212                           str_replace(
213                               str_replace(
214                                   str_replace(
215                                       str_replace(
216                                           str_replace(
217                                               str_replace(
218                                                   str_replace(
219                                                       str_replace(inline_default, '0', ''),
220                                                       '1', ''),
221                                                   '2', ''),
222                                               '3', ''),
223                                           '4', ''),
224                                       '5', ''),
225                                   '6', ''),
226                               '7', ''),
227                           '8', ''),
228                       '9', '') = "DEFAULT  ")
229               OR
230               (str_replace(
231                       str_replace(
232                           str_replace(
233                               str_replace(
234                                   str_replace(
235                                       str_replace(
236                                           str_replace(
237                                               str_replace(
238                                                   str_replace(
239                                                       str_replace(inline_default, '0', ''),
240                                                       '1', ''),
241                                                   '2', ''),
242                                               '3', ''),
243                                           '4', ''),
244                                       '5', ''),
245                                   '6', ''),
246                               '7', ''),
247                           '8', ''),
248                       '9', '') = "DEFAULT  +")
249               OR
250               (str_replace(
251                       str_replace(
252                           str_replace(
253                               str_replace(
254                                   str_replace(
255                                       str_replace(
256                                           str_replace(
257                                               str_replace(
258                                                   str_replace(
259                                                       str_replace(inline_default, '0', ''),
260                                                       '1', ''),
261                                                   '2', ''),
262                                               '3', ''),
263                                           '4', ''),
264                                       '5', ''),
265                                   '6', ''),
266                               '7', ''),
267                           '8', ''),
268                       '9', '') = "DEFAULT  -")
269               OR
270               (str_replace(
271                       str_replace(
272                           str_replace(
273                               str_replace(
274                                   str_replace(
275                                       str_replace(
276                                           str_replace(
277                                               str_replace(
278                                                   str_replace(
279                                                       str_replace(inline_default, '0', ''),
280                                                       '1', ''),
281                                                   '2', ''),
282                                               '3', ''),
283                                           '4', ''),
284                                       '5', ''),
285                                   '6', ''),
286                               '7', ''),
287                           '8', ''),
288                       '9', '') = "DEFAULT  .")
289               OR
290               (str_replace(
291                       str_replace(
292                           str_replace(
293                               str_replace(
294                                   str_replace(
295                                       str_replace(
296                                           str_replace(
297                                               str_replace(
298                                                   str_replace(
299                                                       str_replace(inline_default, '0', ''),
300                                                       '1', ''),
301                                                   '2', ''),
302                                               '3', ''),
303                                           '4', ''),
304                                       '5', ''),
305                                   '6', ''),
306                               '7', ''),
307                           '8', ''),
308                       '9', '') = "DEFAULT  +.")
309               OR
310               (str_replace(
311                       str_replace(
312                           str_replace(
313                               str_replace(
314                                   str_replace(
315                                       str_replace(
316                                           str_replace(
317                                               str_replace(
318                                                   str_replace(
319                                                       str_replace(inline_default, '0', ''),
320                                                       '1', ''),
321                                                   '2', ''),
322                                               '3', ''),
323                                           '4', ''),
324                                       '5', ''),
325                                   '6', ''),
326                               '7', ''),
327                           '8', ''),
328                       '9', '') = "DEFAULT  -."))
329   
330       SELECT @unique_def = count(*),
331           @tot_def = isnull(sum(dup_cnt), 0)
332       FROM #defval_tab
333   
334       if @report_only != "NO"
335       begin
336           print ' '
337           print '==============================================================='
338           print 'sp_merge_dup_inline_default is used to identify duplicate'
339           print 'inline default objects, subsequently to convert one of them'
340           print 'into sharable inline default object and remove the rest.'
341           print 'As the result, it will remove entries from sysobjects,'
342           print 'syscomments and sysprocedures. It will also update entries'
343           print 'in syscolumns, syscomments and sysprocedures.'
344           print ' '
345           print 'Following is the current state of your inline default objects'
346           print 'found out by sp_merge_dup_inline_default and what it could'
347           print 'potentially do to them. By default, sp_merge_dup_inline_default'
348           print 'only reports the current state and this warning message. If you'
349           print 'really intend to carry out the changes, please rerun this'
350           print 'stored procedure using '
351           print 'sp_merge_dup_inline_default @report_only = "NO"'
352           print ' '
353   
354           SELECT @msg = 'Database ' + @dbname + ' has about ' + ltrim(str(@unique_def)) + ' unique inline defaults'
355           print @msg
356   
357           print 'If you convert them into sharable inline defaults, the rest of'
358   
359           SELECT @msg = 'total ' + ltrim(str(@tot_def)) + ' duplicate defaults can be removed from the system catalogs.'
360           print @msg
361   
362           print '==============================================================='
363   
364           return (0)
365       end
366   
367       SELECT @tot_def_removed = 0
368       SELECT @tot_sharable_converted = 0
369       SELECT @cur_prog = 0
370       SELECT @num_of_milestones = 0
371       SELECT @num_of_secondary_milestones = 0
372   
373       /* 
374       ** Decide how to show progress if needed. 
375       ** It is only approximate and quite rough. Just to give some
376       ** visual feedback if needed.
377       */
378       IF @unique_def > 64
379       BEGIN
380           SELECT @dot_per_milestone = 1
381           SELECT @prog_milestone = case when @unique_def % 64 = 0
382                   then @unique_def / 64
383                   else (@unique_def + 64) / 64
384               end
385           SELECT @num_of_secondary_milestones = 64 - (@unique_def / @prog_milestone)
386       END
387       ELSE
388       BEGIN
389           IF @unique_def > 0
390           BEGIN
391               SELECT @dot_per_milestone = 64 / @unique_def
392               SELECT @prog_milestone = 1
393               SELECT @num_of_secondary_milestones = 64 % (@unique_def * @dot_per_milestone)
394           END
395       END
396       IF @num_of_secondary_milestones != 0
397       BEGIN
398           SELECT @prog_secondary_milestone = (@unique_def / @prog_milestone) / @num_of_secondary_milestones
399       END
400       ELSE
401       BEGIN
402           SELECT @prog_secondary_milestone = 0
403       END
404   
405       IF @show_progress = 'YES'
406       BEGIN
407           PRINT 'Converting...'
408   
409           SELECT @msg = '[#' + replicate(' ', 64) + ']'
410           PRINT @msg
411       END
412   
413       /* 
414       ** we will carry out the conversion in a transactional manner
415       ** for each unique default. Once the work for one unique default
416       ** is done, we commit and move onto the next one.
417       */
418       DECLARE defval_cur CURSOR FOR
419       SELECT uid, inline_default
420       FROM #defval_tab
421       ORDER BY dup_cnt
422       FOR READ ONLY
423   
424       OPEN defval_cur
425   
426       /*
427       ** go through each one and remove the duplicate ones.
428       */
429       FETCH defval_cur INTO @user_id, @def_val
430       WHILE @@sqlstatus = 0
431       BEGIN
432           IF @cur_prog = @prog_milestone
433           BEGIN
434               SELECT @num_of_milestones = @num_of_milestones + 1
435               SELECT @cur_prog = 0
436   
437               IF @show_progress = 'YES'
438               BEGIN
439                   SELECT @msg = '[#' + replicate('#', @dot_per_milestone * @num_of_milestones)
440                   IF @num_of_secondary_milestones > 0
441                       and
442                       @num_of_milestones / @prog_secondary_milestone > 0
443                   BEGIN
444                       SELECT @msg = @msg + replicate('#', @num_of_milestones / @prog_secondary_milestone)
445                   END
446                   SELECT @msg = @msg + replicate(' ', 64) + ']'
447                   SELECT @msg = left (@msg, 66) + ']'
448                   PRINT @msg
449               END
450           END
451   
452           /* locate all duplicate default objects with the same @def_val */
453           INSERT INTO #dupdef_tab
454           SELECT D.id
455           FROM dbo.syscomments D, dbo.sysobjects O, dbo.sysprocedures P
456           WHERE rtrim(case when right (D.text, 1) = char(10)
457                       then left (D.text, len(D.text) - 1)
458                       else D.text
459                   end) = @def_val
460               and D.id = O.id
461               and O.type = 'D'
462               and O.uid = @user_id
463               and O.id = P.id
464               and P.type = 2
465               and P.sequence = 0
466               and P.number = 0
467               and P.status & 4096 = 4096
468               and D.number = 0
469               and D.colid = 1
470               and D.colid2 = 0
471               and (SELECT count(*)
472                   FROM dbo.syscomments DD
473                   WHERE DD.id = O.id) <= 1
474           PLAN "(insert (nl_join (nl_join (nested 
475     (scan (table (D [dbo.syscomments])))
476     (subq (scalar_agg (i_scan csyscomments (table (DD [dbo.syscomments]))))))
477     (i_scan csysobjects (table (O [dbo.sysobjects]))))
478     (i_scan csysprocedures (table (P [dbo.sysprocedures])))))"
479   
480           /* pick one of them to be converted to a sharable one */
481           SELECT TOP 1 @def_id = D.id, @def_name = O.name
482           FROM #dupdef_tab D, dbo.sysobjects O
483           WHERE D.id = O.id
484               and charindex("[", O.name) = 0
485               and charindex("]", O.name) = 0
486               and datalength("create default [" + rtrim(O.name) + "] as " + right (@def_val, len(@def_val) - 9) + char(10)) <= 255
487   
488           If @@rowcount != 1
489           BEGIN
490               SELECT @cur_prog = @cur_prog + 1
491               TRUNCATE TABLE #dupdef_tab
492               FETCH defval_cur INTO @user_id, @def_val
493               CONTINUE
494           END
495   
496           /* the rest will be removed from system catalogs */
497           DELETE #dupdef_tab
498           WHERE id = @def_id
499   
500           /* sharable inline default has a slightly different text representation */
501           SELECT @new_def_text = "create default [" + rtrim(@def_name) + "] as " + right (@def_val, len(@def_val) - 9) + char(10)
502   
503           UPDATE INDEX STATISTICS #dupdef_tab
504   
505           SELECT @rollback = 1
506           BEGIN TRAN shrink_db
507   
508           /* first change existing columns to only use the sharable one */
509           UPDATE syscolumns
510           SET cdefault = @def_id
511           FROM syscolumns
512           WHERE cdefault in (SELECT id from #dupdef_tab)
513   
514           IF @@error = 0
515           BEGIN
516               /* next delete the duplicate entries from sysprocedures */
517               DELETE sysprocedures
518               WHERE id in (select id from #dupdef_tab)
519               PLAN "(delete (nl_join (distinct_sorted (i_scan i2 #dupdef_tab)) 
520             (i_scan csysprocedures sysprocedures)))"
521   
522               IF @@error = 0
523               BEGIN
524                   /* ... also from syscomments */
525                   DELETE syscomments
526                   WHERE id in (select id from #dupdef_tab)
527   
528                   IF @@error = 0
529                   BEGIN
530                       /* ... also from sysobjects. */
531                       DELETE sysobjects
532                       WHERE id in (select id from #dupdef_tab)
533   
534                       IF @@error = 0
535                       BEGIN
536                           /* finally convert the chosen one into sharable inline default */
537                           UPDATE sysprocedures
538                           SET status = status & ~ 4096
539                           FROM sysprocedures
540                           WHERE id = @def_id
541                           PLAN "(update (i_scan csysprocedures sysprocedures))"
542   
543                           IF @@error = 0
544                           BEGIN
545                               UPDATE syscomments
546                               SET status = status | 8,
547                                   text = @new_def_text
548                               FROM syscomments
549                               WHERE id = @def_id
550                               PLAN "(update (i_scan csyscomments syscomments))"
551   
552                               IF @@error = 0
553                               BEGIN
554                                   commit tran shrink_db
555                                   set @rollback = @@error
556                               END
557                           END
558                       END
559                   END
560               END
561           END
562   
563           /* 
564           ** If any of the above steps failed, we need to rollback all the changes
565           ** made for this unique default, which will also lead to the ending of this
566           ** SP run. However the ealier committed changes (if any) for different
567           ** unique defaults are already completed. If user immediately re-runs
568           ** the SP now, the conversion will "resume" from the failed unique default.
569           ** Typical reasons for failure are out of system resource (such as locks) or
570           ** manual abort.
571           */
572           IF @rollback != 0
573           BEGIN
574               ROLLBACK TRAN shrink_db
575               BREAK
576           END
577           ELSE
578           BEGIN
579               SELECT @tot_def_removed = @tot_def_removed + count(*)
580               FROM #dupdef_tab
581   
582               SELECT @tot_sharable_converted = @tot_sharable_converted + 1
583           END
584   
585           SELECT @cur_prog = @cur_prog + 1
586           TRUNCATE TABLE #dupdef_tab
587           FETCH defval_cur INTO @user_id, @def_val
588       END
589   
590       CLOSE defval_cur
591       DEALLOCATE CURSOR defval_cur
592   
593       IF @show_progress = 'YES'
594       BEGIN
595           SELECT @msg = '[#' + replicate('#', 64) + ']'
596           PRINT @msg
597           PRINT ' '
598       END
599   
600       IF @tot_sharable_converted > 0
601       BEGIN
602           SELECT @msg = 'Total ' + ltrim(str(@tot_def_removed)) + ' duplicate defaults are removed and ' + ltrim(str(@tot_sharable_converted)) + ' defaults'
603           print @msg
604           print 'are converted to sharable inline defaults.'
605   
606           /*
607           ** 17006, "Database is modified and in single-user mode."
608           */
609           exec sp_getmessage 17006, @msg output
610           print @msg
611   
612           /*
613           ** 17795, "System Administrator (SA) must reset it to multi-user mode with sp_dboption."
614           */
615           exec sp_getmessage 17795, @msg output
616           print @msg
617       END
618       ELSE
619       BEGIN
620           print 'Database is not modified. Please try it later if duplicate inline '
621           print 'defaults do exist and the current resource limitation is preventing '
622           print 'this conversion process.'
623       END
624   
625       return (0)
626   


exec sp_procxmode 'sp_merge_dup_inline_default', 'AnyMode'
go

Grant Execute on sp_merge_dup_inline_default to public
go
DEFECTS
 QJWI 5 Join or Sarg Without Index 117
 MCTR 4 Conditional Begin Tran or Commit Tran 506
 MCTR 4 Conditional Begin Tran or Commit Tran 554
 MEST 4 Empty String will be replaced by Single Space 219
 MEST 4 Empty String will be replaced by Single Space 220
 MEST 4 Empty String will be replaced by Single Space 221
 MEST 4 Empty String will be replaced by Single Space 222
 MEST 4 Empty String will be replaced by Single Space 223
 MEST 4 Empty String will be replaced by Single Space 224
 MEST 4 Empty String will be replaced by Single Space 225
 MEST 4 Empty String will be replaced by Single Space 226
 MEST 4 Empty String will be replaced by Single Space 227
 MEST 4 Empty String will be replaced by Single Space 228
 MEST 4 Empty String will be replaced by Single Space 239
 MEST 4 Empty String will be replaced by Single Space 240
 MEST 4 Empty String will be replaced by Single Space 241
 MEST 4 Empty String will be replaced by Single Space 242
 MEST 4 Empty String will be replaced by Single Space 243
 MEST 4 Empty String will be replaced by Single Space 244
 MEST 4 Empty String will be replaced by Single Space 245
 MEST 4 Empty String will be replaced by Single Space 246
 MEST 4 Empty String will be replaced by Single Space 247
 MEST 4 Empty String will be replaced by Single Space 248
 MEST 4 Empty String will be replaced by Single Space 259
 MEST 4 Empty String will be replaced by Single Space 260
 MEST 4 Empty String will be replaced by Single Space 261
 MEST 4 Empty String will be replaced by Single Space 262
 MEST 4 Empty String will be replaced by Single Space 263
 MEST 4 Empty String will be replaced by Single Space 264
 MEST 4 Empty String will be replaced by Single Space 265
 MEST 4 Empty String will be replaced by Single Space 266
 MEST 4 Empty String will be replaced by Single Space 267
 MEST 4 Empty String will be replaced by Single Space 268
 MEST 4 Empty String will be replaced by Single Space 279
 MEST 4 Empty String will be replaced by Single Space 280
 MEST 4 Empty String will be replaced by Single Space 281
 MEST 4 Empty String will be replaced by Single Space 282
 MEST 4 Empty String will be replaced by Single Space 283
 MEST 4 Empty String will be replaced by Single Space 284
 MEST 4 Empty String will be replaced by Single Space 285
 MEST 4 Empty String will be replaced by Single Space 286
 MEST 4 Empty String will be replaced by Single Space 287
 MEST 4 Empty String will be replaced by Single Space 288
 MEST 4 Empty String will be replaced by Single Space 299
 MEST 4 Empty String will be replaced by Single Space 300
 MEST 4 Empty String will be replaced by Single Space 301
 MEST 4 Empty String will be replaced by Single Space 302
 MEST 4 Empty String will be replaced by Single Space 303
 MEST 4 Empty String will be replaced by Single Space 304
 MEST 4 Empty String will be replaced by Single Space 305
 MEST 4 Empty String will be replaced by Single Space 306
 MEST 4 Empty String will be replaced by Single Space 307
 MEST 4 Empty String will be replaced by Single Space 308
 MEST 4 Empty String will be replaced by Single Space 319
 MEST 4 Empty String will be replaced by Single Space 320
 MEST 4 Empty String will be replaced by Single Space 321
 MEST 4 Empty String will be replaced by Single Space 322
 MEST 4 Empty String will be replaced by Single Space 323
 MEST 4 Empty String will be replaced by Single Space 324
 MEST 4 Empty String will be replaced by Single Space 325
 MEST 4 Empty String will be replaced by Single Space 326
 MEST 4 Empty String will be replaced by Single Space 327
 MEST 4 Empty String will be replaced by Single Space 328
 MINU 4 Unique Index with nullable columns tempdb..#defval_tab_crby_sybsystemprocs__sp_merge_dup_inline_default tempdb..#defval_tab
 MTYP 4 Assignment type mismatch @bitdesc: varchar(30) = varchar(255) 113
 MTYP 4 Assignment type mismatch text: varchar(255) = varchar(300) 547
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
116
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysprocedures.csysprocedures unique clustered
(id, number, type, sequence)
Intersection: {type, number, sequence}
153
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: syscomments.csyscomments unique clustered
(id, number, colid2, colid, texttype)
Intersection: {texttype, colid, number, colid2}
157
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: #defval_tab.i1 unique
(uid, inline_default)
Intersection: {inline_default}
174
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: #defval_tab.i1 unique
(uid, inline_default)
Intersection: {inline_default}
186
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: syscomments.csyscomments unique clustered
(id, number, colid2, colid, texttype)
Intersection: {colid, number, colid2}
456
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {uid}
461
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysprocedures.csysprocedures unique clustered
(id, number, type, sequence)
Intersection: {type, number, sequence}
464
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 115
 QTYP 4 Comparison type mismatch smallint = int 115
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 153
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 155
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 157
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 158
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 159
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 160
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 464
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 466
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 468
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 469
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 470
 TNOU 4 Table with no unique index master..spt_values master..spt_values
 MAW1 3 Warning message on %name% sybsystemprocs..syscomments.id: Warning message on syscomments 150
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 150
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 151
 MAW1 3 Warning message on %name% sybsystemprocs..sysprocedures.id: Warning message on sysprocedures 151
 MAW1 3 Warning message on %name% sybsystemprocs..syscomments.id: Warning message on syscomments 163
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 163
 MAW1 3 Warning message on %name% sybsystemprocs..syscomments.id: Warning message on syscomments 454
 MAW1 3 Warning message on %name% sybsystemprocs..syscomments.id: Warning message on syscomments 460
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 460
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 463
 MAW1 3 Warning message on %name% sybsystemprocs..sysprocedures.id: Warning message on sysprocedures 463
 MAW1 3 Warning message on %name% sybsystemprocs..syscomments.id: Warning message on syscomments 473
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 473
 MAW1 3 Warning message on %name% tempdb..#dupdef_tab.id: Warning message on #dupdef_tab_crby_sybsystemprocs__sp_merge_dup_inline_default 481
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 483
 MAW1 3 Warning message on %name% tempdb..#dupdef_tab.id: Warning message on #dupdef_tab_crby_sybsystemprocs__sp_merge_dup_inline_default 483
 MAW1 3 Warning message on %name% tempdb..#dupdef_tab.id: Warning message on #dupdef_tab_crby_sybsystemprocs__sp_merge_dup_inline_default 498
 MAW1 3 Warning message on %name% tempdb..#dupdef_tab.id: Warning message on #dupdef_tab_crby_sybsystemprocs__sp_merge_dup_inline_default 512
 MAW1 3 Warning message on %name% sybsystemprocs..sysprocedures.id: Warning message on sysprocedures 518
 MAW1 3 Warning message on %name% tempdb..#dupdef_tab.id: Warning message on #dupdef_tab_crby_sybsystemprocs__sp_merge_dup_inline_default 518
 MAW1 3 Warning message on %name% sybsystemprocs..syscomments.id: Warning message on syscomments 526
 MAW1 3 Warning message on %name% tempdb..#dupdef_tab.id: Warning message on #dupdef_tab_crby_sybsystemprocs__sp_merge_dup_inline_default 526
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 532
 MAW1 3 Warning message on %name% tempdb..#dupdef_tab.id: Warning message on #dupdef_tab_crby_sybsystemprocs__sp_merge_dup_inline_default 532
 MAW1 3 Warning message on %name% sybsystemprocs..sysprocedures.id: Warning message on sysprocedures 540
 MAW1 3 Warning message on %name% sybsystemprocs..syscomments.id: Warning message on syscomments 549
 MGTP 3 Grant to public master..spt_values  
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public sybsystemprocs..sp_merge_dup_inline_default  
 MGTP 3 Grant to public sybsystemprocs..syscolumns  
 MGTP 3 Grant to public sybsystemprocs..syscomments  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MGTP 3 Grant to public sybsystemprocs..sysprocedures  
 MNER 3 No Error Check should check @@error after insert 140
 MNER 3 No Error Check should check @@error after delete 173
 MNER 3 No Error Check should check @@error after delete 185
 MNER 3 No Error Check should check @@error after insert 453
 MNER 3 No Error Check should check @@error after truncate 491
 MNER 3 No Error Check should check @@error after delete 497
 MNER 3 No Error Check should check @@error after truncate 586
 MNER 3 No Error Check should check return value of exec 609
 MNER 3 No Error Check should check return value of exec 615
 MUCO 3 Useless Code Useless Brackets 53
 MUCO 3 Useless Code Useless Brackets 81
 MUCO 3 Useless Code Useless Brackets 83
 MUCO 3 Useless Code Useless Brackets 84
 MUCO 3 Useless Code Useless Brackets 91
 MUCO 3 Useless Code Useless Brackets 92
 MUCO 3 Useless Code Useless Brackets 104
 MUCO 3 Useless Code Useless Brackets 124
 MUCO 3 Useless Code Useless Brackets 364
 MUCO 3 Useless Code Useless Brackets 385
 MUCO 3 Useless Code Useless Brackets 625
 MUIN 3 Column created using implicit nullability 127
 MUIN 3 Column created using implicit nullability 129
 MUUF 3 Update or Delete with Useless From Clause 537
 MUUF 3 Update or Delete with Useless From Clause 545
 QAFM 3 Var Assignment from potentially many rows 481
 QFAP 3 Force abstract plan ABSTRACT_PLAN_CLAUSE PLAN..."(insert (group_hashing (nested (nl_join (nl_join (t_scan (table (O [dbo.sysobjects]))) (i_scan csyscomments (table (D [dbo.syscomments])))) (i_scan csysprocedures (table (P [dbo.sysprocedures])))) (subq (scalar_agg (i_scan csyscomments (table (DD [dbo.syscomments]))))))))" 141
 QFAP 3 Force abstract plan ABSTRACT_PLAN_CLAUSE PLAN..."(insert (nl_join (nl_join (nested (scan (table (D [dbo.syscomments]))) (subq (scalar_agg (i_scan csyscomments (table (DD [dbo.syscomments])))))) (i_scan csysobjects (table (O [dbo.sysobjects])))) (i_scan csysprocedures (table (P [dbo.sysprocedures])))))" 454
 QFAP 3 Force abstract plan ABSTRACT_PLAN_CLAUSE PLAN..."(delete (nl_join (distinct_sorted (i_scan i2 #dupdef_tab)) (i_scan csysprocedures sysprocedures)))" 517
 QFAP 3 Force abstract plan ABSTRACT_PLAN_CLAUSE PLAN..."(update (i_scan csysprocedures sysprocedures))" 537
 QFAP 3 Force abstract plan ABSTRACT_PLAN_CLAUSE PLAN..."(update (i_scan csyscomments syscomments))" 545
 QISO 3 Set isolation level 60
 QJWT 3 Join or Sarg Without Index on temp table 512
 QJWT 3 Join or Sarg Without Index on temp table 518
 QJWT 3 Join or Sarg Without Index on temp table 526
 QJWT 3 Join or Sarg Without Index on temp table 532
 QNAJ 3 Not using ANSI Inner Join 114
 QNAJ 3 Not using ANSI Inner Join 147
 QNAJ 3 Not using ANSI Inner Join 455
 QNAJ 3 Not using ANSI Inner Join 482
 QPNC 3 No column in condition 161
 QPNC 3 No column in condition 471
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {name}
484
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysprocedures.csysprocedures unique clustered
(id, number, type, sequence)
Intersection: {id}
540
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscomments.csyscomments unique clustered
(id, number, colid2, colid, texttype)
Intersection: {id}
549
 VNRD 3 Variable is not read @nullarg 77
 VNRD 3 Variable is not read @dummy 88
 CRDO 2 Read Only Cursor Marker (has for read only clause) 419
 MDRV 2 Derived Table Marker 142
 MSUC 2 Correlated Subquery Marker 161
 MSUC 2 Correlated Subquery Marker 471
 MTR1 2 Metrics: Comments Ratio Comments: 18% 14
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 29 = 33dec - 6exi + 2 14
 MTR3 2 Metrics: Query Complexity Complexity: 297 14
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscomments, o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprocedures} 0 142
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscomments, o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprocedures} 0 142
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscomments, o=sybsystemprocs..sysobjects} 0 161
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscomments, o=sybsystemprocs..sysobjects} 0 161
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscomments, o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprocedures} 0 454
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscomments, o=sybsystemprocs..sysobjects} 0 471

DEPENDENCIES
PROCS AND TABLES USED
read_writes table sybsystemprocs..syscomments  
reads table master..sysdatabases (1)  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..sysconfigures (1)  
   reads table master..syscurconfigs (1)  
read_writes table tempdb..#defval_tab (1) 
read_writes table sybsystemprocs..sysprocedures  
writes table sybsystemprocs..syscolumns  
calls proc sybsystemprocs..sp_getmessage  
   reads table master..sysmessages (1)  
   reads table sybsystemprocs..sysusermessages  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..syslanguages (1)  
reads table master..spt_values (1)  
read_writes table sybsystemprocs..sysobjects  
read_writes table tempdb..#dupdef_tab (1)