DatabaseProcApplicationCreatedLinks
sybsystemprocssp_estspace  31 Aug 14Defects Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     /*
4     ** Messages for "sp_estspace"
5     ** 17461, "Object does not exist in this database."
6     ** 18054, "Table contains text/image type columns but length for these columns not specified in the argument list.
7     */
8     
9     create procedure sp_estspace
10    
11    /*	A procedure to estimate the disk space requirements of a table
12    **	and its associated indexes
13    **	Written by Malcolm Colton with assistance from Hal Spitz
14    */
15    (@table_name varchar(511) = null, /* name of table to estimate */
16        @no_of_rows float = 0, /* number of rows in the table */
17        @fill_factor float = null, /* the fill factor */
18        @cols_to_max varchar(2060) = null,
19        /* variable length columns for which
20        to use the maximum rather than 50% of
21        the maximum length. It can hold
22        at most 8 column names.*/
23        @textbin_len float = null, /* len of all text & binary data/row */
24        @iosec float = 30,
25        @pagesize varchar(30) = null)
26    as
27    
28        declare @msg varchar(120)
29    
30        if @@trancount = 0
31        begin
32            set chained off
33        end
34    
35        set transaction isolation level 1
36    
37        /*	Give usage statement if @table_name is null */
38    
39        if @table_name is null or @no_of_rows <= 0 or @fill_factor < 0 or @fill_factor > 100
40        begin
41            print 'USAGE:'
42            print 'sp_estspace table_name, no_of_rows, fill_factor, cols_to_max, textbin_len, iosec'
43            print 'where '
44            print '      table_name  is the name of the table,'
45            print '      no_of_rows  is the number of rows in the table (>0),'
46            print '      fill_factor is the index fill factor. Values range from'
47            print '                   0 to 100. (default = 0,in which case internal'
48            print '                   fill factor will be used)'
49            print '      cols_to_max is a list of the variable length columns for which'
50            print '                   to use the maximum length instead of the average'
51            print '			  (default = null)'
52            print '      textbin_len is length of all the text and binary fields per'
53            print '                   row ( default =  0).'
54            print '      iosec       is the number of I/Os per second on this machine'
55            print '                   (default = 30)'
56            print 'Examples: sp_estspace titles, 10000, 50, "title, notes", null, 25'
57            print '          sp_estspace titles, 50000'
58            print '          sp_estspace titles, 50000, null, null, null, 40'
59            return
60        end
61    
62        declare @sum_fixed int,
63            @sum_var int,
64            @sum_avgvar int,
65            @table_id int,
66            @num_var int,
67            @data_pages float,
68            @sysstat smallint,
69            @temp float,
70            @temp1 float,
71            @index_id int,
72            @last_id int,
73            @i int,
74            @level_pages float,
75            @key varchar(255),
76            @usertype tinyint,
77            @type tinyint,
78            @level smallint,
79            @vartype smallint,
80            @more bit,
81            @next_level float,
82            @rows_per_page int,
83            @row_len int,
84            @length int,
85            @index_name varchar(255),
86            @page_size int,
87            @page_K int,
88            @index_type varchar(20),
89            @mrpg smallint,
90            @factor float,
91            @status2 int,
92            @sysstat2 int,
93            @table_type varchar(3),
94            @maxcols_in_key int,
95            @status int,
96            @unit_loc int /* The position of the unit in the
97        ** pagesize parameter string.
98        */
99    
100       select @sum_fixed = 0,
101           @sum_var = 0,
102           @sum_avgvar = 0,
103           @table_id = 0,
104           @num_var = 0,
105           @data_pages = 0,
106           @row_len = 0,
107           @sysstat = 0,
108           @sysstat2 = 0,
109           @maxcols_in_key = 31 /* max. columns allowed in an index */
110   
111       set nocount on
112   
113       /* set the default fill_factor */
114       if @fill_factor is null
115           select @fill_factor = 0
116   
117       /* Make sure table exists */
118   
119       select @sysstat = sysstat,
120           @table_id = id,
121           @sysstat2 = sysstat2
122       from sysobjects where id = object_id(@table_name)
123   
124       if @sysstat & 7 not in (1, 3)
125       begin
126           /* 17461, "Object does not exist in this database." */
127           raiserror 17461
128           return (1)
129       end
130   
131       /* Get the lock scheme for the table */
132       if @sysstat & 15 in (1, 3)
133       begin
134           /*
135           ** the bits 0x2000, 0x4000 & 0x8000 represents any
136           ** explicit lock scheme bits that can be set, so
137           ** get them out ( 0x2000 + 0x4000 + 0x8000 = 57344)
138           */
139           select @sysstat2 = (@sysstat2 & 57344)
140   
141           if (@sysstat2 in (0, 8192, 16384, 32768))
142           begin
143               if (@sysstat2 = 8192 or @sysstat2 = 0)
144               begin
145                   /* Lock scheme is Allpages */
146                   select @table_type = "AP"
147               end
148               if (@sysstat2 = 16384 or @sysstat2 = 32768)
149               begin
150                   /* Lock scheme is Data only */
151                   select @table_type = "DOL"
152               end
153           end
154           else
155           begin
156               /* 17579, Lock scheme Unknown or Corrupted */
157               raiserror 17579
158               return (1)
159           end
160       end
161   
162       /* If the user provided a pagesize, use it */
163       if (@pagesize is not NULL)
164       begin
165           /*
166           ** Did the user provide a unit with the pagesize parameter?
167           ** The units accepted are kilobytes, anything else is a usage error.
168           */
169           select @unit_loc = patindex("%[kK]%", @pagesize)
170   
171           if (@unit_loc = 0)
172           begin
173               select @page_size = convert(int, @pagesize)
174               select @page_K = @page_size / 1024
175           end
176           else
177           begin
178               exec @status = sp_aux_getsize @pagesize, @page_K output
179   
180               if (@status = 0)
181               begin
182                   /*
183                   ** Invalid syntax
184                   ** 18940 "Error: Specified pagesize '%1!' is
185                   ** invalid. Valid values are '2048', '4096' '8192',
186                   ** '16384', or '2k', '4k', '8k', '16k'.  Unit
187                   ** specifiers can also be 'K'."
188                   */
189                   raiserror 18940, @pagesize
190                   return (1)
191               end
192   
193               /* sp_aux_getsize returns value in K unit. */
194               select @page_size = @page_K * 1024
195   
196           end
197   
198           if (@page_size != 2048 and @page_size != 4096 and
199                   @page_size != 8192 and @page_size != 16384)
200           begin
201   
202               /*
203               ** Invalid syntax
204               ** 18940 "Error: Specified pagesize '%1!' is
205               ** invalid. Valid values are '2048', '4096' '8192',
206               ** '16384', or '2k', '4k', '8k', '16k'.  Unit
207               ** specifiers can also be 'K'."
208               */
209               raiserror 18940, @pagesize
210               return (1)
211           end
212   
213       end
214       else
215       begin
216   
217           /* Get machine page size if user did not specify one */
218           select @page_size = low
219           from master.dbo.spt_values
220           where type = 'E'
221               and number = 1
222           select @page_K = @page_size / 1024
223       end
224   
225   
226       if @table_type = "AP"
227           select @page_size = @page_size - 32
228       else
229           select @page_size = @page_size - 44
230   
231       if @fill_factor != 0
232           select @fill_factor = @fill_factor / 100.0
233   
234       /* Get the max_rows_per_page value */
235       select @mrpg = maxrowsperpage
236       from sysindexes
237       where id = @table_id and indid in (0, 1)
238   
239       /* Allpages tables are limited to 255 rows per page */
240       if @table_type = 'AP' and (@mrpg = 0 or @mrpg > 255)
241           select @mrpg = 255
242   
243       /* max_rows_per_page option is not valid for DOL tables */
244       if (@table_type = 'DOL')
245       begin
246           select @mrpg = 0
247       end
248   
249       /* Create tables for results */
250   
251       create table #results
252       (name varchar(255),
253           type varchar(12),
254           idx_level smallint,
255           pages float,
256           Kbytes float)
257   
258       create table #times
259       (name varchar(255),
260           type varchar(12) null,
261           tot_pages float null,
262           time_mins float null)
263   
264   
265       /* Create table of column info for the table to be estimated */
266   
267       select length, type, name
268       into #col_table
269       from syscolumns
270       where id = @table_id
271   
272   
273       /* check if text/image lenth specified if these fields exist in the table */
274       if (@textbin_len is null)
275       begin
276           if exists (select name from syscolumns where id = @table_id and
277                       (type = 35 or type = 34))
278   
279           begin
280               /* 18054, "Table contains text/image type columns but length for 
281               ** these columns not specified in the argument list.
282               */
283               raiserror 18054
284               return (1)
285           end
286       end
287   
288   
289       /* Look up the important values from this table */
290   
291       /* get fixed length columns (those with types other than 39, 37 (varbinary)) */
292       select @sum_fixed = isnull(sum(length), 0)
293       from #col_table
294       where type not in (37, 39)
295   
296       /* get var. length columns that have to use the entire size */
297       select @num_var = isnull(count(*), 0), @sum_var = isnull(sum(length), 0)
298       from #col_table
299       where type in (37, 39)
300           and charindex(name, @cols_to_max) > 0
301   
302       /* get var. length columns for which average size is used */
303       select @num_var = @num_var + isnull(count(*), 0),
304           @sum_avgvar = isnull(sum(length / 2), 0)
305       from #col_table
306       where type in (37, 39)
307           and charindex(name, @cols_to_max) = 0
308   
309       /* Calculate the data page requirements */
310       set arithabort numeric_truncation off
311   
312       /* variables used for DOL tables */
313       declare @data_fixed_ovhd smallint,
314           @data_var_ovhd smallint,
315           @data_len int, /* length of the data portion */
316           @dol_ind_fixed_ovhd smallint,
317           @dol_ind_var_ovhd smallint,
318           @dol_ind_data_len int, /* length of row in index*/
319           @next_level_rows float
320   
321       select @data_len = @sum_fixed + @sum_var + @sum_avgvar
322       /* If the table is DOL */
323       if (@table_type = "DOL")
324       begin
325           /*
326           ** Overhead if there are no variable length columns:
327           ** 2 bytes each for row number, status, varcount, row offset table
328           ** entry.
329           **
330           ** Overhead if variable length columns are present:
331           ** 2 bytes each for row number, status, varcount, row offset table
332           ** entry and rowlength.
333           ** offset table size is 2 bytes per varcol, no adjust table.
334           */
335           select @data_fixed_ovhd = 8.0,
336               @data_var_ovhd = 10.0 + (2 * @num_var)
337       end
338       else
339       begin
340           /*
341           ** Overhead if there are no variable length columns:
342           ** 1 byte each for varcount and row number and 2 bytes for row
343           ** offset table entry.
344           **
345           ** Overhead if variable length columns are present:
346           ** 1 byte each for varcount and row number, and 2 bytes for 
347           ** row offset table entry and rowlength.
348           ** offset table size is 1 byte per varcol + 1 byte for end of 
349           ** data offset. Adjust table size is rowlength/256 + 1.
350           */
351           select @data_fixed_ovhd = 4.0,
352               @data_var_ovhd = 6.0 + (@num_var + 1) + (@data_len / 256 + 1)
353       end
354       if @num_var = 0
355           select @row_len = @data_fixed_ovhd + @data_len
356       else
357           select @row_len = @data_var_ovhd + @data_len
358       /*
359       ** For tables marked for 'incremental transfer', add additional 8 bytes
360       ** for the per row timestamp used for the transfer.
361       */
362       declare @xfer_stat int
363       select @xfer_stat = number
364       from master.dbo.spt_values
365       where type = 'O3' and name = "incremental transfer on"
366   
367       if exists (select 1 from sysobjects
368               where id = @table_id and (sysstat3 & @xfer_stat = @xfer_stat))
369       begin
370           select @row_len = @row_len + 8
371       end
372   
373       /*
374       ** For DOL datapages, the min. row length should be 10 (2bytes each for row 
375       ** number and status + 6 bytes of space for (possible) forwarding address) 
376       ** In the above computation of rowlength we have included the row offset 
377       ** table entry of 2 bytes, we need 10 bytes minimum length excluding these
378       ** 2 bytes.
379       */
380       if @table_type = "DOL"
381       begin
382           if (@row_len - 2) < 10
383               select @row_len = 12
384       end
385   
386       /* Allow for fill-factor if set to other than zero */
387   
388       if @fill_factor = 0
389       begin
390           select @temp = convert(float, @no_of_rows) *
391               (convert(float, @row_len) / convert(float, @page_size))
392       end
393       else
394       begin
395           select @temp = convert(float, @no_of_rows) /
396               (convert(float, @page_size) * convert(float, @fill_factor))
397           select @temp = convert(float, @row_len) * @temp
398       end
399   
400       /* 
401       ** If there is maxrowsperpage and it creates less number of pages than
402       ** without it, then use it.
403       */
404       if @mrpg != 0
405       begin
406           select @temp1 = convert(float, @no_of_rows) /
407               convert(float, @mrpg)
408           if @temp < @temp1
409               select @temp = @temp1
410       end
411   
412       /* Now add in allocation pages */
413       select @temp = @temp + (@temp / 256.0)
414       select @data_pages = @temp + 1.0
415       if @data_pages < 8.0
416           select @data_pages = 8.0
417   
418       insert #results values
419       (@table_name, 'data', 0, @data_pages, @data_pages * @page_K)
420   
421       /* calculate the pages for text and length */
422   
423       /* text/bin pages write multiples of 450 bytes per page */
424       if (@textbin_len is not null)
425       begin
426           select @temp = ceiling(@textbin_len / (floor(@page_size / 450.0) * 450.0))
427           select @temp = @temp * @no_of_rows
428           insert #results values
429           (@table_name, 'text/image', 0, @temp, @temp * @page_K)
430       end
431   
432       /* See if the table has any indexes */
433   
434       select @index_id = min(indid)
435       from sysindexes
436       where id = @table_id
437           and indid > 0 and indid < 255
438   
439       if @index_id is null /* We've finished if there are no indexes */
440       begin
441           select @msg = @table_name + ' has no indexes'
442           print @msg
443   
444           select name, type, idx_level, 'Pages' = str(pages, 12, 0), 'Kbytes' = str(Kbytes, 12, 0)
445           into #results_1
446           from #results
447   
448           exec sp_autoformat @fulltabname = #results_1
449   
450           select Total_Mbytes = str(sum(Kbytes) / 1024.0, 17, 2)
451           from #results
452   
453           drop table #results_1
454           drop table #results
455           return
456       end
457   
458       select @sum_fixed = 0,
459           @sum_var = 0,
460           @num_var = 0,
461           @temp = 0
462   
463       /* For each index, calculate the important variables
464       ** use them to calculate the index size, and print it */
465   
466       while @index_id is not null
467       begin
468           select @index_name = name, @mrpg = maxrowsperpage,
469               @status2 = status2
470           from sysindexes
471           where id = @table_id
472               and indid = @index_id
473           if @index_id = 1
474           begin
475               select @index_type = 'clustered'
476           end
477           else
478           if @index_id > 1
479           begin
480               if (@status2 & 512 = 512)
481               begin
482                   select @index_type = 'clustered'
483               end
484               else
485               begin
486                   select @index_type = 'nonclustered'
487               end
488           end
489   
490           select @num_var = 0,
491               @sum_var = 0,
492               @sum_fixed = 0
493   
494           if (@table_type = "DOL")
495           begin
496               select @mrpg = 0
497           end
498   
499           select @i = 1
500   
501           /* Look up each of the key fields for the index */
502   
503           while @i <= @maxcols_in_key
504           begin
505               select @key = index_col(@table_name, @index_id, @i)
506   
507               if @key is null
508                   break
509               else /* Process one key field */
510               begin
511                   select @type = type, @length = length, @vartype = offset
512                   from syscolumns
513                   where id = @table_id
514                       and name = @key
515   
516                   if @vartype < 0
517                       select @num_var = @num_var + 1
518                   else
519                       select @sum_fixed = @sum_fixed + @length
520   
521                   /* varchar, varbinary: check if in @cols_to_max */
522                   if (@type = 37 or @type = 39)
523                   begin
524                       if charindex(@key, @cols_to_max) = 0
525                           select @sum_var = @sum_var + (@length / 2)
526                       else
527                           select @sum_var = @sum_var + @length
528                   end
529               end
530   
531               select @i = @i + 1 /* Get next key field in this index */
532           end
533   
534           /* Calculate the space used by this index */
535   
536           /* 
537           ** For indexes of DOL TABLES, we assume the following: 
538           ** No suffix compression is done, Assumes that all the
539           ** keys in the index are unique and there is no difference
540           ** between clustered and nonclustered index
541           */
542           select @dol_ind_fixed_ovhd = 9
543           select @dol_ind_var_ovhd = 9 + (2 * @num_var)
544           select @dol_ind_data_len = @sum_fixed + @sum_var
545           if @table_type = "DOL"
546           begin
547               if @num_var = 0
548                   select @row_len = @dol_ind_fixed_ovhd + @dol_ind_data_len
549               else
550                   select @row_len = @dol_ind_var_ovhd + @dol_ind_data_len
551           end
552           else /* AP locked object */
553           begin
554               if @index_id = 1
555               begin
556                   if @num_var = 0
557                       select @row_len = 5 + @sum_fixed
558                   else
559                       select @row_len = @sum_fixed + @sum_var + @num_var + 8
560               end
561               else
562               begin
563                   if @num_var = 0
564                       select @row_len = 7 + @sum_fixed
565                   else
566                       select @row_len = @sum_fixed + @sum_var + @num_var + 11
567               end
568           end
569   
570           /* Allow for fill-factor if set to other than zero */
571   
572           if @fill_factor = 0 or @index_id = 1
573           begin
574               select @rows_per_page = @page_size / @row_len - 2
575           end
576           else
577           begin
578               select @rows_per_page = @page_size / @row_len * @fill_factor
579           end
580   
581           /* at lease one row per page */
582           if @rows_per_page < 1
583               select @rows_per_page = 1
584   
585           /* Prestage index building information */
586           select @more = 1, @level = 0
587   
588           /* For DOL indexes, estimate the #levels and #pages */
589           if @table_type = "DOL"
590           begin
591               while @more = 1
592               begin
593                   if @level = 0
594                   begin
595                       if @mrpg != 0
596                           select @rows_per_page = @mrpg
597                       select @level_pages = @no_of_rows /
598                           convert(float, @rows_per_page)
599                       select @next_level_rows = @level_pages
600                   end
601                   else if @level > 255
602                   begin
603                       /* This index is too big to build */
604                       raiserror 19414, @index_name
605                       goto kill_proc
606                   end
607                   else
608                   begin
609                       select @level_pages = @next_level_rows /
610                           convert(float, @rows_per_page)
611                       select @next_level_rows = @level_pages
612                   end
613   
614                   /* Account for allocation/oam pages */
615                   select @level_pages = @level_pages +
616                       (@level_pages / 256.0) + 1.0
617                   /* Insert the row into the results table */
618                   insert #results values
619                   (@index_name, @index_type, @level,
620                       @level_pages,
621                       floor(@level_pages) * @page_K)
622                   select @level = @level + 1
623                   if @next_level_rows <= @rows_per_page
624                       select @more = 0
625               end
626               /* Account for the root page */
627               insert #results values
628               (@index_name, @index_type, @level, 1, @page_K)
629           end
630           else
631           begin /* AP locked object */
632               if @rows_per_page > 256
633                   select @rows_per_page = 256
634               /* 
635               ** For clustered indexes, the first level of index is 
636               ** based on the number of data pages. 
637               ** For nonclustered, it is the number of data rows
638               */
639   
640               if @index_id = 1
641                   select @next_level = @data_pages
642               else
643                   select @next_level = @no_of_rows
644               while @more = 1
645               begin
646                   if @index_id != 1 and @level = 0 and @mrpg != 0
647                       select @rows_per_page = @mrpg
648   
649                   /* 
650                   **  No fill factor for non-leaf nodes, 
651                   **  recalculate rows/page 
652                   */
653                   if @index_id != 1 and @level = 1
654                   begin
655                       select @row_len = @row_len + 4
656                       select @rows_per_page = @page_size / @row_len - 2
657                       if @rows_per_page > 256
658                           select @rows_per_page = 256
659                   end
660   
661                   if @level > 255
662                   begin
663                       /* This index is too big to build */
664                       raiserror 19414, @index_name
665                       goto kill_proc
666                   end
667   
668                   /* 
669                   **  calculate the number of pages at a single index 
670                   **  level 
671                   */
672                   select @temp = @next_level /
673                       convert(float, @rows_per_page)
674   
675                   /* Add in a factor for allocation pages */
676                   select @temp = @temp + (@temp / 256.0) + 1.0
677   
678                   select @level_pages = @temp
679   
680                   insert #results values
681                   (@index_name, @index_type, @level, @level_pages,
682                       floor(@level_pages) * @page_K)
683   
684                   select @next_level = @level_pages
685                   select @level = @level + 1
686   
687                   /* see if we can fit the next level in 1 page */
688                   if @rows_per_page >= @next_level
689                       select @more = 0
690               end
691               /* Account for single root page */
692               if @level_pages > 1
693                   insert #results values
694                   (@index_name, @index_type, @level, 1, @page_K)
695           end
696   
697   
698           /* Now look for next index id for this table */
699   
700           select @last_id = @index_id
701           select @index_id = null
702           select @index_id = min(indid)
703           from sysindexes
704           where id = @table_id
705               and indid > @last_id
706               and indid < 255
707   
708       end
709   
710       select name, type, idx_level, 'Pages' = str(pages, 12, 0), 'Kbytes' = str(Kbytes, 12, 0)
711       into #results_2
712       from #results
713   
714       exec sp_autoformat @fulltabname = #results_2
715   
716       drop table #results_2
717   
718       select Total_Mbytes = str(sum(Kbytes) / 1024.0, 17, 2)
719       from #results
720   
721       /* Get ready to calculate creation times for the indexes */
722   
723       insert #times(name, tot_pages)
724       select name, sum(pages)
725       from #results
726       where type != 'data'
727       group by name
728   
729       /* Get index type from #results */
730       update #times
731       set #times.type = #results.type
732       from #times, #results
733       where #times.name = #results.name
734   
735       /* Add data pages to size of clustered index */
736       update #times
737       set tot_pages = tot_pages + @data_pages
738       where type = 'clustered'
739   
740       /* Calculate for clustered index */
741   
742       select @factor = ceiling((log(@data_pages) / 2.07944) - 1.8813)
743       update #times
744       set time_mins = ((2 * @factor) + 1) * @data_pages / 60 / @iosec
745       where type = 'clustered'
746   
747       /* Calculate for non-clustered index */
748   
749       update #times
750       set time_mins = ((2 * @factor * tot_pages) + @data_pages)
751           / 60 / @iosec
752       where time_mins is null
753   
754       select name, type, 'total_pages' = str(tot_pages, 12, 0), 'time_mins' = str(time_mins, 12, 0)
755       into #times_1
756       from #times
757   
758       exec sp_autoformat @fulltabname = #times_1
759   
760       drop table #times_1
761   
762   kill_proc:
763   
764       drop table #results
765       drop table #col_table
766       drop table #times
767   
768       return
769   


exec sp_procxmode 'sp_estspace', 'AnyMode'
go

Grant Execute on sp_estspace to public
go
RESULT SETS
sp_estspace_rset_002
sp_estspace_rset_001

DEFECTS
 MTYP 4 Assignment type mismatch name: varchar(255) = varchar(511) 419
 MTYP 4 Assignment type mismatch name: varchar(255) = varchar(511) 429
 MTYP 4 Assignment type mismatch type: varchar(12) = varchar(20) 619
 MTYP 4 Assignment type mismatch type: varchar(12) = varchar(20) 628
 MTYP 4 Assignment type mismatch type: varchar(12) = varchar(20) 681
 MTYP 4 Assignment type mismatch type: varchar(12) = varchar(20) 694
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 448
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 714
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 758
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
365
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 277
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 437
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 472
 QTYP 4 Comparison type mismatch smallint = int 472
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 705
 QTYP 4 Comparison type mismatch smallint = int 705
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 706
 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 sybsystemprocs..sp_estspace  
 MGTP 3 Grant to public sybsystemprocs..syscolumns  
 MGTP 3 Grant to public sybsystemprocs..sysindexes  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MNER 3 No Error Check should check @@error after select into 267
 MNER 3 No Error Check should check @@error after insert 418
 MNER 3 No Error Check should check @@error after insert 428
 MNER 3 No Error Check should check @@error after select into 444
 MNER 3 No Error Check should check return value of exec 448
 MNER 3 No Error Check should check @@error after insert 618
 MNER 3 No Error Check should check @@error after insert 627
 MNER 3 No Error Check should check @@error after insert 680
 MNER 3 No Error Check should check @@error after insert 693
 MNER 3 No Error Check should check @@error after select into 710
 MNER 3 No Error Check should check return value of exec 714
 MNER 3 No Error Check should check @@error after insert 723
 MNER 3 No Error Check should check @@error after update 730
 MNER 3 No Error Check should check @@error after update 736
 MNER 3 No Error Check should check @@error after update 743
 MNER 3 No Error Check should check @@error after update 749
 MNER 3 No Error Check should check @@error after select into 754
 MNER 3 No Error Check should check return value of exec 758
 MUCO 3 Useless Code Useless Brackets in create proc 15
 MUCO 3 Useless Code Useless Brackets 128
 MUCO 3 Useless Code Useless Brackets 141
 MUCO 3 Useless Code Useless Brackets 143
 MUCO 3 Useless Code Useless Brackets 148
 MUCO 3 Useless Code Useless Brackets 158
 MUCO 3 Useless Code Useless Brackets 163
 MUCO 3 Useless Code Useless Brackets 171
 MUCO 3 Useless Code Useless Brackets 180
 MUCO 3 Useless Code Useless Brackets 190
 MUCO 3 Useless Code Useless Brackets 198
 MUCO 3 Useless Code Useless Brackets 210
 MUCO 3 Useless Code Useless Brackets 244
 MUCO 3 Useless Code Useless Brackets 274
 MUCO 3 Useless Code Useless Brackets 284
 MUCO 3 Useless Code Useless Brackets 323
 MUCO 3 Useless Code Useless Brackets 336
 MUCO 3 Useless Code Useless Brackets 413
 MUCO 3 Useless Code Useless Brackets 424
 MUCO 3 Useless Code Useless Brackets 480
 MUCO 3 Useless Code Useless Brackets 494
 MUCO 3 Useless Code Useless Brackets 522
 MUCO 3 Useless Code Useless Brackets 525
 MUCO 3 Useless Code Useless Brackets 543
 MUCO 3 Useless Code Useless Brackets 616
 MUCO 3 Useless Code Useless Brackets 676
 MUCO 3 Useless Code Useless Brackets 742
 MUCO 3 Useless Code Useless Brackets 744
 MUCO 3 Useless Code Useless Brackets 750
 MUIN 3 Column created using implicit nullability 251
 MUIN 3 Column created using implicit nullability 258
 QAFM 3 Var Assignment from potentially many rows 218
 QAFM 3 Var Assignment from potentially many rows 363
 QAFM 3 Var Assignment from potentially many rows 511
 QCRS 3 Conditional Result Set 450
 QCTC 3 Conditional Table Creation 444
 QISO 3 Set isolation level 35
 QIWC 3 Insert with not all columns specified missing 2 columns out of 4 723
 QJWT 3 Join or Sarg Without Index on temp table 733
 QNAJ 3 Not using ANSI Inner Join 732
 QNUA 3 Should use Alias: Table #results 732
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
270
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
276
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
513
 VUNU 3 Variable is not used @usertype 76
 MRST 2 Result Set Marker 450
 MRST 2 Result Set Marker 718
 MSUB 2 Subquery Marker 276
 MSUB 2 Subquery Marker 367
 MTR1 2 Metrics: Comments Ratio Comments: 31% 9
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 84 = 90dec - 8exi + 2 9
 MTR3 2 Metrics: Query Complexity Complexity: 380 9

DEPENDENCIES
PROCS AND TABLES USED
writes table tempdb..#results_2 (1) 
reads table sybsystemprocs..syscolumns  
reads table sybsystemprocs..sysobjects  
reads table master..spt_values (1)  
read_writes table tempdb..#col_table (1) 
calls proc sybsystemprocs..sp_autoformat  
   reads table tempdb..systypes (1)  
   calls proc sybsystemprocs..sp_autoformat  
   reads table master..syscolumns (1)  
   reads table tempdb..syscolumns (1)  
   calls proc sybsystemprocs..sp_namecrack  
   reads table master..systypes (1)  
   read_writes table tempdb..#colinfo_af (1) 
writes table tempdb..#results_1 (1) 
read_writes table tempdb..#results (1) 
reads table sybsystemprocs..sysindexes  
read_writes table tempdb..#times (1) 
writes table tempdb..#times_1 (1) 
calls proc sybsystemprocs..sp_aux_getsize