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


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 95
 MCTR 4 Conditional Begin Tran or Commit Tran 484
 MCTR 4 Conditional Begin Tran or Commit Tran 532
 MEST 4 Empty String will be replaced by Single Space 197
 MEST 4 Empty String will be replaced by Single Space 198
 MEST 4 Empty String will be replaced by Single Space 199
 MEST 4 Empty String will be replaced by Single Space 200
 MEST 4 Empty String will be replaced by Single Space 201
 MEST 4 Empty String will be replaced by Single Space 202
 MEST 4 Empty String will be replaced by Single Space 203
 MEST 4 Empty String will be replaced by Single Space 204
 MEST 4 Empty String will be replaced by Single Space 205
 MEST 4 Empty String will be replaced by Single Space 206
 MEST 4 Empty String will be replaced by Single Space 217
 MEST 4 Empty String will be replaced by Single Space 218
 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 237
 MEST 4 Empty String will be replaced by Single Space 238
 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 257
 MEST 4 Empty String will be replaced by Single Space 258
 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 277
 MEST 4 Empty String will be replaced by Single Space 278
 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 297
 MEST 4 Empty String will be replaced by Single Space 298
 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
 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 text: varchar(255) = varchar(300) 525
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
94
 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}
131
 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}
135
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: #defval_tab.i1 unique
(uid, inline_default)
Intersection: {inline_default}
152
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: #defval_tab.i1 unique
(uid, inline_default)
Intersection: {inline_default}
164
 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}
434
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {uid}
439
 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}
442
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 93
 QTYP 4 Comparison type mismatch smallint = int 93
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 131
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 133
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 135
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 136
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 137
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 138
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 442
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 444
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 446
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 447
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 448
 TNOU 4 Table with no unique index master..spt_values master..spt_values
 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 118
 MNER 3 No Error Check should check @@error after delete 151
 MNER 3 No Error Check should check @@error after delete 163
 MNER 3 No Error Check should check @@error after insert 431
 MNER 3 No Error Check should check @@error after truncate 469
 MNER 3 No Error Check should check @@error after delete 475
 MNER 3 No Error Check should check @@error after truncate 564
 MNER 3 No Error Check should check return value of exec 587
 MNER 3 No Error Check should check return value of exec 593
 MUCO 3 Useless Code Useless Brackets 48
 MUCO 3 Useless Code Useless Brackets 64
 MUCO 3 Useless Code Useless Brackets 65
 MUCO 3 Useless Code Useless Brackets 82
 MUCO 3 Useless Code Useless Brackets 102
 MUCO 3 Useless Code Useless Brackets 342
 MUCO 3 Useless Code Useless Brackets 363
 MUCO 3 Useless Code Useless Brackets 603
 MUIN 3 Column created using implicit nullability 105
 MUIN 3 Column created using implicit nullability 107
 MUUF 3 Update or Delete with Useless From Clause 515
 MUUF 3 Update or Delete with Useless From Clause 523
 QAFM 3 Var Assignment from potentially many rows 459
 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]))))))))" 119
 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])))))" 432
 QFAP 3 Force abstract plan ABSTRACT_PLAN_CLAUSE PLAN..."(delete (nl_join (distinct_sorted (i_scan i2 #dupdef_tab)) (i_scan csysprocedures sysprocedures)))" 495
 QFAP 3 Force abstract plan ABSTRACT_PLAN_CLAUSE PLAN..."(update (i_scan csysprocedures sysprocedures))" 515
 QFAP 3 Force abstract plan ABSTRACT_PLAN_CLAUSE PLAN..."(update (i_scan csyscomments syscomments))" 523
 QISO 3 Set isolation level 55
 QJWT 3 Join or Sarg Without Index on temp table 461
 QJWT 3 Join or Sarg Without Index on temp table 490
 QJWT 3 Join or Sarg Without Index on temp table 496
 QJWT 3 Join or Sarg Without Index on temp table 504
 QJWT 3 Join or Sarg Without Index on temp table 510
 QNAJ 3 Not using ANSI Inner Join 92
 QNAJ 3 Not using ANSI Inner Join 125
 QNAJ 3 Not using ANSI Inner Join 433
 QNAJ 3 Not using ANSI Inner Join 460
 QPNC 3 No column in condition 139
 QPNC 3 No column in condition 449
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {name}
462
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysprocedures.csysprocedures unique clustered
(id, number, type, sequence)
Intersection: {id}
518
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscomments.csyscomments unique clustered
(id, number, colid2, colid, texttype)
Intersection: {id}
527
 CRDO 2 Read Only Cursor Marker (has for read only clause) 397
 MDRV 2 Derived Table Marker 120
 MSUC 2 Correlated Subquery Marker 139
 MSUC 2 Correlated Subquery Marker 449
 MTR1 2 Metrics: Comments Ratio Comments: 17% 14
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 27 = 30dec - 5exi + 2 14
 MTR3 2 Metrics: Query Complexity Complexity: 285 14
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscomments, o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprocedures} 0 120
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscomments, o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprocedures} 0 120
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscomments, o=sybsystemprocs..sysobjects} 0 139
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscomments, o=sybsystemprocs..sysobjects} 0 139
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscomments, o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprocedures} 0 432
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscomments, o=sybsystemprocs..sysobjects} 0 449

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