DatabaseProcApplicationCreatedLinks
sybsystemprocssp_plan_dbccdb  14 déc. 14Defects Propagation Dependencies

1     
2     /*
3     ** Messages for "sp_plan_dbccdb"	
4     **
5     ** 17421, "No such database -- run sp_helpdb to list databases."
6     ** 17230, "You must be the System Administrator (SA) or the Database Owner (dbo) to execute this procedure."
7     ** 17260, "Can't run %1! from within a transaction."
8     ** 18454, "Recommended size for %1! database is %2!MB (data = %3!MB, log = %4!MB).", 
9     ** 18456, "Recommended devices for %1! are:"
10    ** 18457, "No suitable devices for %1! in master..sysdevices."
11    ** 18458, "Recommended values for workspace size, cache size and worker process count are:"
12    ** 18459, "%1! database already exists with size %2!MB."
13    */
14    
15    create or replace procedure sp_plan_dbccdb(
16        @dbname varchar(255) = null)
17    as
18        declare @dbid smallint, @dbid1 smallint, @dbid2 smallint
19        declare @dbcc_dbid int
20        declare @scanws_size bigint
21        declare @textws_size bigint
22        declare @cache_size bigint
23        declare @wt_count smallint
24        declare @devsize bigint
25        declare @name varchar(255)
26        declare @ldev varchar(255), @physdev varchar(255)
27        declare @dbcc_dbname varchar(255)
28        declare @dbccdb_size bigint
29        declare @log_size bigint, @data_size bigint
30        declare @size double precision
31        declare @msg varchar(1024)
32        declare @min_wssize bigint /* minimum size of workspace */
33        declare @new_min_wssize bigint /* revised minimum size of workspace */
34        declare @min_cachesize bigint /* minimum size of cache */
35        declare @KB_per_page int /* number of KB per page */
36        declare @divideby int
37        declare @displayunit char
38        declare @divideby_cm int
39        declare @displayunit_cm char
40        declare @dbo int
41        declare @dummy int
42        declare @scratchdb sysname
43        declare @qrystr varchar(312)
44        declare @adb_stat int
45        declare @comp_size bigint /* compression pool size */
46        declare @comp_stat int /* compression status */
47        declare @compdata_stat int /* data compression status */
48        declare @complog_stat int /* log compression status */
49        declare @status3 int /* status3 from sysdatabases */
50        declare @adb_dbid int /* archive database id */
51        declare @nullarg varchar(1)
52        declare @gp_enabled int
53        declare @permstatus int
54    
55        /*
56        ** These variables are used for formatting the output
57        */
58        declare @str1 char(30), @str2 char(8), @str3 char(8)
59        declare @str4 char(6), @str5 char(9), @str6 char(20)
60        declare @longstr varchar(255)
61        declare @tempcol varchar(30)
62    
63        select @nullarg = NULL
64        /*
65        ** Disallow this procedure within a transaction since it 
66        ** creates temporary tables
67        */
68        if @@trancount > 0
69        begin
70            /*
71            ** 17260, "Can't run %1! from within a transaction."
72            */
73            raiserror 17260, "sp_plan_dbccdb"
74            return (1)
75        end
76        else
77        begin
78            set chained off
79        end
80    
81        set transaction isolation level 1
82        set nocount on
83    
84        /* Get the number of KB per page */
85        select @KB_per_page = @@maxpagesize / 1024
86    
87        /* Locate the archive database status */
88        select @adb_stat = number from master.dbo.spt_values
89        where name = "archive database" and type = "D3"
90    
91        select @compdata_stat = number from master.dbo.spt_values
92        where name = "compressed data" and type = "D3"
93    
94        select @complog_stat = number from master.dbo.spt_values
95        where name = "compressed log" and type = "D3"
96    
97        select @comp_stat = @compdata_stat | @complog_stat
98    
99        /*
100       ** create tables for storing temporary results and declare
101       ** cursors on them
102       */
103       create table #dbcc_dblist(dbid int, ldev_name varchar(30), status3 int)
104   
105       declare cursor_dbcc_dblist cursor for
106       select dbid, status3 from #dbcc_dblist
107   
108       /*
109       ** Insert dbid and devname for all valid normal databases in 
110       ** #dbcc_dblist. The status3 is used to find whether an archive
111       ** database has compressed data or log. Set it to 0 for non-archive
112       ** databases.
113       */
114       insert #dbcc_dblist(dbid, ldev_name, status3)
115       select distinct su.dbid, dev.name, 0
116       from master..sysdevices dev, master..sysusages su,
117           master..sysdatabases sd
118       where su.dbid = sd.dbid and dev.cntrltype = 0 and
119           su.vdevno = dev.vdevno and
120           sd.status3 & @adb_stat = 0
121   
122       /*
123       ** Add dbid and devname for all the archive databases.
124       */
125       declare cursor_adb cursor for
126       select dbid, status3 from master.dbo.sysdatabases
127       where (status3 & @adb_stat) != 0
128   
129       open cursor_adb
130       fetch cursor_adb into @adb_dbid, @status3
131       while (@@sqlstatus = 0)
132       begin
133           /*
134           ** Read the scratch database name from sysattributes.
135           */
136           select @scratchdb = convert(sysname, char_value)
137           from master.dbo.sysattributes
138           where class = 28
139               and object_type = "D"
140               and object = @adb_dbid
141               and attribute = 0
142   
143           if (db_id(@scratchdb) is not null)
144           begin
145               /*
146               ** Add dump devices (location = 5) to the list
147               ** of used devices.
148               */
149               select @qrystr = 'insert #dbcc_dblist(dbid, ldev_name, status3) ' +
150                   'select distinct @adb_dbid, dev.name, @status3 ' +
151                   'from master..sysdevices dev, ' +
152                   @scratchdb + '.dbo.sysaltusages sa ' +
153                   'where sa.dbid=@adb_dbid and ' +
154                   'sa.location = 5 and ' +
155                   'dev.status & 4 = 4 and ' +
156                   'sa.vdevno = dev.vdevno '
157               exec (@qrystr)
158           end
159           fetch cursor_adb into @adb_dbid, @status3
160       end
161       close cursor_adb
162       deallocate cursor cursor_adb
163   
164       create table #dbcc_devlist(ldev_name varchar(255), devsize bigint, flag int)
165   
166       /*
167       ** Get logical and physical names and size in KB of all physical devices
168       ** in the system that have atleast 1MB space and set the flag to 0
169       */
170       declare @numpages bigint
171       declare @devname char(255)
172       declare c_dbcc_devlist cursor for select name, (high - low) + 1
173       from master..sysdevices
174       where cntrltype = 0 and ((high - low) + 1) >= 512 and status & 4 = 0
175   
176       open c_dbcc_devlist
177       fetch c_dbcc_devlist into @devname, @numpages
178       while (@@sqlstatus = 0)
179       begin
180   
181           insert into #dbcc_devlist(ldev_name, devsize, flag) values
182           (@devname, @numpages * @KB_per_page, 0)
183   
184           fetch c_dbcc_devlist into @devname, @numpages
185   
186       end
187   
188       close c_dbcc_devlist
189       deallocate c_dbcc_devlist
190   
191       /*
192       ** For each device in #dbcc_devlist, set the flag to 1 if the device
193       ** is being used by a database in #dbcc_dblist
194       */
195       update #dbcc_devlist set flag = 1
196       from #dbcc_dblist dl, #dbcc_devlist dev
197       where dl.ldev_name = dev.ldev_name
198   
199       /* Validate the given database */
200       select @dbo = 0
201       if (@dbname is not null)
202       begin
203           select @dbid = dbid
204           from master..sysdatabases where name = @dbname
205   
206           if (@dbid is null)
207           begin
208               /*
209               ** 17421, "No such database -- run sp_helpdb to list databases."
210               */
211               raiserror 17421
212               return (1)
213           end
214   
215           delete from #dbcc_dblist where dbid != @dbid
216       end
217   
218       /*
219       ** Only the Database Owner (DBO) or
220       ** Accounts with SA role can execute it.
221       ** If granular permission is enabled, any 
222       ** user is able to execute it.
223       */
224       execute @permstatus = sp_aux_checkroleperm "sa_role",
225           @nullarg, @dbname, @gp_enabled output
226   
227       if (@gp_enabled = 0 and @permstatus != 0)
228       begin
229           /* Check if it's DBO of target DB */
230           if (@dbname is not null)
231           begin
232               exec sybsystemprocs.dbo.sp_is_valid_user @dbo output, @dbname
233           end
234           if (@dbo != 1)
235           begin
236               /*
237               ** Generate the appropriate audit record.
238               */
239               select @dummy = proc_role("sa_role")
240   
241               if (@dbname is null)
242                   /*
243                   ** 18990, "You must be the System 
244                   ** Administrator (SA) to execute this
245                   **  procedure with a NULL database name
246                   ** (dbname) parameter."
247                   */
248                   raiserror 18990
249               else
250                   /*
251                   ** 17230, "You must be the System
252                   ** Administrator (SA) or the Database 
253                   ** Owner (dbo) to execute this procedure."
254                   */
255                   raiserror 17230
256   
257               return (1)
258           end
259       end
260   
261       /*
262       ** The minimum size of a workspace is 24 pages
263       */
264       select @min_wssize = 24
265   
266       /*
267       ** Create a temporary table for storing the configuration information
268       */
269       create table #dbcc_config(dbid int, dbname varchar(30) null,
270           scanws_size bigint null, textws_size bigint null,
271           wt_count smallint null, cache_size bigint null,
272           comp_size bigint null)
273       declare cursor_dbcc_config cursor for
274       select dbname, scanws_size, textws_size, wt_count,
275           cache_size, comp_size
276       from #dbcc_config
277   
278       /*
279       ** The data_size determines the amount of space set to store fault data
280       ** and statistics data for each checkstorage run. 
281       ** We multiply this by 5 so that there enough space to store 5 instances 
282       ** of results. If the dbcc database is dbccalt, we need space only for 
283       ** storing results from dbccdb, otherwise we need space to store results 
284       ** from all databases in master..sysdatabase except model, tempdb and 
285       ** sybsystemprocs. log_size is set to 23MB (This is an arbitrary size.)
286       */
287       if (@dbname = "dbccdb")
288       begin
289           select @dbcc_dbname = "dbccalt"
290           select @log_size = 23552
291           select @data_size = 10000
292       end
293       else
294       begin
295           select @dbcc_dbname = "dbccdb"
296           select @log_size = 23552
297           /*
298           ** Select all databases except model, tempdb and sybsystemprocs
299           */
300           select @data_size = 10000 * (count(*) - 3)
301           from master..sysdatabases
302       end
303   
304       /*
305       ** For each dbid in #dbcc_dblist table, calculate the configuration parameters
306       */
307       open cursor_dbcc_dblist
308       fetch cursor_dbcc_dblist into @dbid, @status3
309       while (@@sqlstatus = 0)
310       begin
311   
312           /*
313           ** Get the database name
314           */
315           select @name = db_name(@dbid)
316   
317           /*
318           ** check if there is already an entry for this database. 
319           ** If so, continue with the next entry. The table #dbcc_dblist
320           ** can have multiple rows for a database if that databse resides
321           ** on multiple devices. We need to calculate the parameters
322           ** only once.
323           */
324           if exists (select 1 from #dbcc_config where dbid = @dbid)
325           begin
326               fetch cursor_dbcc_dblist into @dbid, @status3
327               continue
328           end
329   
330           /*
331           ** Get the number of devices used by this database
332           ** Scale the number of worker processes based on the device count
333           */
334           select @wt_count = count(*) from #dbcc_dblist
335           where #dbcc_dblist.dbid = @dbid
336   
337           if (@wt_count < 1)
338               select @wt_count = 1
339           else
340           if (@wt_count > 128)
341               select @wt_count = 128
342   
343           /*
344           ** Minimum cache size is 640K * @wt_count
345           */
346           select @min_cachesize = 640 * @wt_count
347   
348           /*
349           ** The minimum workspace size depends on the number of worker
350           ** processes too. So adjust the value.
351           */
352           select @new_min_wssize = ((@wt_count + 1) * 8)
353           if (@new_min_wssize < @min_wssize)
354               select @new_min_wssize = @min_wssize
355   
356           /*
357           ** Get the scanws size for this database which is 1.2% of 
358           ** database size. It is in units of pages.
359           ** If it's an archive database, the disk map has to be
360           ** found in sysaltusages in the scratch database.
361           */
362           if exists (select * from master.dbo.sysdatabases
363                   where dbid = @dbid
364                       and (status3 & @adb_stat) != 0)
365           begin
366               /*
367               ** The original diskmap is stored in the sysaltusages catalog
368               ** in the scratch database with a location = 4.
369               ** Read the scratch database name from sysattributes first.
370               */
371               select @scratchdb = convert(sysname, char_value)
372               from master.dbo.sysattributes
373               where class = 28
374                   and object_type = "D"
375                   and object = @dbid
376                   and attribute = 0
377   
378               select @qrystr =
379                   'select @scanws_size=ceiling(sum(size) * 0.012) from ' +
380                   @scratchdb + '.dbo.sysaltusages ' +
381                   'where dbid=@dbid and location = 4'
382               exec (@qrystr)
383   
384               /*
385               ** Set recommended compression pool size to 4MB per thread
386               ** if database is compressed.
387               */
388               if ((@status3 & @comp_stat) != 0)
389               begin
390                   select @comp_size = 4096 * @wt_count
391               end
392               else
393               begin
394                   select @comp_size = 0
395               end
396           end
397           else
398           begin
399               select @scanws_size = ceiling(sum(size) * 0.012)
400               from master..sysusages
401               where dbid = @dbid
402               select @comp_size = 0
403           end
404   
405           /*
406           ** set the size to the next multiple of 8 which is the unit
407           ** of allocation
408           */
409           select @scanws_size = ((@scanws_size / 8) + 1) * 8
410           /*
411           ** Set the text workspace size to 25% of scan workspace and round
412           ** it off to a multiple of 8.
413           */
414           select @textws_size = ceiling(@scanws_size * 0.25)
415           select @textws_size = ((@textws_size / 8) + 1) * 8
416   
417           /*
418           ** The minimum size required for workspaces are @new_min_wssize pages.
419           ** Set the sizes to @new_min_wssize if the current value is less 
420           ** than this.
421           */
422           if (@scanws_size < @new_min_wssize)
423           begin
424               select @scanws_size = @new_min_wssize
425           end
426           if (@textws_size < @new_min_wssize)
427           begin
428               select @textws_size = @new_min_wssize
429           end
430   
431           /*
432           ** For small databases (size < 20MB), increase the size of 
433           ** scanws by 8 pages. This is because checkstorage uses one 
434           ** full extent (8 pages) for page mapping. As a result 1.2%
435           ** of database size may not be enough for scanws if database is
436           ** small.
437           */
438           if (@scanws_size <= 128)
439               select @scanws_size = @scanws_size + 8
440           /*
441           ** convert workspace sizes into KB
442           */
443           select @scanws_size = @scanws_size * @KB_per_page
444           select @textws_size = @textws_size * @KB_per_page
445   
446           /*
447           ** Set the cache size to 20% of total workspace size.
448           ** The cache size is actually the size of 16K(or 64K) buffer
449           ** pool. The actual cache size must be @cache_size + 512K
450           **
451           ** NOTE: since scanws ~ Sum(max two db size) * 1.2%
452           ** 		textws ~ scanws/4
453           **	 then these quantities are comparable in size.
454           **	 Cache will be set to 20% of the sum, so it
455           **	 will always be less magnitude than these
456           **	 quantities. So later, we can safely use
457           **	 a single scale factor, divideby, so scale the
458           **	 display unit. If however, these units are allowed
459           **	 in the future to become asymmetric (eg vastly
460           **	 different in magnitude) then one should consider
461           ** 	 using the approach in sp_dbcc_evaluatedb to
462           **	 scale the quantities individually.
463           */
464           select @cache_size =
465               ceiling((@scanws_size + @textws_size) * 0.2)
466           if (@cache_size < @min_cachesize)
467           begin
468               select @cache_size = @min_cachesize
469           end
470   
471           /*
472           ** Insert the values calculated for this database into
473           ** #dbcc_config table. This table is used for report generation
474           */
475           insert into #dbcc_config(dbid, dbname, scanws_size, textws_size,
476               wt_count, cache_size, comp_size)
477           values (@dbid, @name, @scanws_size, @textws_size,
478               @wt_count, @cache_size, @comp_size)
479   
480           fetch cursor_dbcc_dblist into @dbid, @status3
481       end
482   
483       close cursor_dbcc_dblist
484   
485       print ""
486   
487       /*
488       ** Calculate (in MB) the size of dbccdb database. Even though 
489       ** checkstorage can be run concurrently on multiple databases,
490       ** it is highly unlikely that users will run checkstorage on
491       ** more than two databases concurrently. So determine the size of
492       ** dbccdb based on the two largest databases in the server.
493       */
494   
495       select @dbid1 = dbid from #dbcc_config
496       group by dbid having sum(scanws_size) = max(sum(scanws_size))
497   
498       select @dbid2 = dbid from #dbcc_config where dbid != @dbid1
499       group by dbid having sum(scanws_size) = max(sum(scanws_size))
500   
501       select @size = (sum(scanws_size) + sum(textws_size))
502       from #dbcc_config
503       where dbid = @dbid1 or dbid = @dbid2
504   
505       /*
506       ** calculate the total size for data and log (in MB). The workspace size 
507       ** is increased by 20% to accommodate future expansion.
508       ** NOTE: ceiling needs a float type to work. Add decimal point to
509       ** 	achieve this.
510       */
511       select @data_size = ceiling((@data_size + (@size * 1.2)) / 1024)
512       select @log_size = ceiling(@log_size / 1024.0)
513       select @dbccdb_size = @data_size + @log_size
514   
515       /*
516       ** 18454, "Recommended size for %1! database is %2!MB (data = %3!MB, log = %4!MB).", 
517       */
518       exec sp_getmessage 18454, @msg output
519       print @msg, @dbcc_dbname, @dbccdb_size, @data_size, @log_size
520   
521       print ""
522   
523       /*
524       ** If @dbcc_dbname exists, report so. Otherwise recommend a device
525       ** for @dbcc_dbname
526       */
527       select @dbcc_dbid = db_id(@dbcc_dbname)
528       if (@dbcc_dbid is not null)
529       begin
530           select @dbccdb_size = (sum(size) * @KB_per_page) / 1024
531           from master..sysusages
532           where dbid = @dbcc_dbid
533           /*
534           ** 18459, "%1! database already exists with size %2!MB"
535           */
536           exec sp_getmessage 18459, @msg output
537           print @msg, @dbcc_dbname, @dbccdb_size
538       end
539       else
540       begin
541   
542           /*
543           ** Delete all devices which are being used by databases.
544           ** This is indicated by flag > 0
545           */
546           delete from #dbcc_devlist where flag != 0
547   
548           /*
549           ** Get the total size of all free devices in MB. If the total
550           ** size is atleast @dbccdb_size, these devices can be used for
551           ** dbcc database.
552           */
553           select @size = (sum(devsize) / 1024) from #dbcc_devlist
554   
555           if (@size >= @dbccdb_size)
556           begin
557               /*
558               ** 18456, "Recommended devices for %1! are:"
559               */
560               exec sp_getmessage 18456, @msg output
561               print @msg, @dbcc_dbname
562               print ""
563               select "Logical Device Name" = ldev_name,
564                   "Device Size (KB)" = devsize
565               from #dbcc_devlist
566           end
567           else
568           begin
569               /*
570               ** 18457, "No suitable devices for %1! in master..sysdevices"
571               */
572               exec sp_getmessage 18457, @msg output
573               print @msg, @dbcc_dbname
574           end
575       end
576       print ""
577   
578       /*
579       ** Format and print the configuration parameters
580       */
581       select @str1 = "dbname"
582       select @str2 = "scan ws"
583       select @str3 = "text ws"
584       select @str4 = "cache"
585       select @str5 = "comp mem"
586       select @str6 = "process count"
587       /*
588       ** 18458, "Recommended values for workspace size, cache size and worker process count are:"
589       */
590       exec sp_getmessage 18458, @msg output
591       print @msg
592       print ""
593       print "%1! %2! %3! %4! %5! %6!",
594           @str1, @str2, @str3, @str4, @str5, @str6
595   
596       open cursor_dbcc_config
597       fetch cursor_dbcc_config into @name, @scanws_size, @textws_size, @wt_count,
598           @cache_size, @comp_size
599   
600   
601       while (@@sqlstatus = 0)
602       begin
603           /*
604           ** sizes are in KB. Find out the appropriate unit to use for
605           ** displaying these values.
606           ** NOTE: problem if size gets larger than MAXINT. This
607           ** represents 2 TB. Change input parameter and sproc
608           ** sp_dbcc_scale_factor if this limit is reached.
609           */
610           execute sp_dbcc_scale_factor @cache_size,
611               @scale_factor = @divideby output,
612               @scale_letter = @displayunit output
613   
614           execute sp_dbcc_scale_factor @comp_size,
615               @scale_factor = @divideby_cm output,
616               @scale_letter = @displayunit_cm output
617   
618           /*
619           ** Convert all values to varchar for formatting purpose
620           */
621           select @str1 = @name
622           select @str2 = convert(varchar(8), ceiling(1.0 * @scanws_size / @divideby))
623               + @displayunit
624           select @str3 = convert(varchar(8), ceiling(1.0 * @textws_size / @divideby))
625               + @displayunit
626           select @str4 = convert(varchar(8), ceiling(1.0 * @cache_size / @divideby))
627               + @displayunit
628           select @str5 = convert(varchar(9), ceiling(1.0 * @comp_size / @divideby_cm))
629               + @displayunit_cm
630           select @str6 = convert(char(3), @wt_count)
631           print "%1! %2! %3! %4! %5! %6!",
632               @str1, @str2, @str3, @str4, @str5, @str6
633           fetch cursor_dbcc_config into @name, @scanws_size,
634               @textws_size, @wt_count, @cache_size, @comp_size
635       end
636       print ""
637   
638       close cursor_dbcc_config
639   
640       /*
641       ** Clean-up all temporary tables
642       */
643       deallocate cursor cursor_dbcc_dblist
644       deallocate cursor cursor_dbcc_config
645   
646       drop table #dbcc_dblist
647       drop table #dbcc_devlist
648       drop table #dbcc_config
649   
650       return (0)
651   
652   


exec sp_procxmode 'sp_plan_dbccdb', 'AnyMode'
go

Grant Execute on sp_plan_dbccdb to public
go
RESULT SETS
sp_plan_dbccdb_rset_001

DEFECTS
 MEST 4 Empty String will be replaced by Single Space 485
 MEST 4 Empty String will be replaced by Single Space 521
 MEST 4 Empty String will be replaced by Single Space 562
 MEST 4 Empty String will be replaced by Single Space 576
 MEST 4 Empty String will be replaced by Single Space 592
 MEST 4 Empty String will be replaced by Single Space 636
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 MTYP 4 Assignment type mismatch dbname: varchar(30) = varchar(255) 477
 MTYP 4 Assignment type mismatch @dbid1: smallint = int 495
 MTYP 4 Assignment type mismatch @dbid2: smallint = int 498
 MTYP 4 Assignment type mismatch @str1: char(30) = varchar(255) 621
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
89
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
92
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
95
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 118
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 138
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 141
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 174
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 215
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 324
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 335
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 373
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 375
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 376
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 498
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 503
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 532
 QTYP 4 Comparison type mismatch smallint = int 532
 TNOU 4 Table with no unique index master..spt_values master..spt_values
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause cursor_adb 126
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause c_dbcc_devlist 172
 MDYN 3 Proc uses Dynamic SQL but is not flagged with Dynamic Ownership Chain 15
 MGTP 3 Grant to public master..spt_values  
 MGTP 3 Grant to public master..sysattributes  
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public master..sysdevices  
 MGTP 3 Grant to public master..sysusages  
 MGTP 3 Grant to public sybsystemprocs..sp_plan_dbccdb  
 MLCH 3 Char type with length>30 char(255) 171
 MNER 3 No Error Check should check @@error after insert 114
 MNER 3 No Error Check should check @@error after insert 181
 MNER 3 No Error Check should check @@error after update 195
 MNER 3 No Error Check should check @@error after delete 215
 MNER 3 No Error Check should check return value of exec 232
 MNER 3 No Error Check should check @@error after insert 475
 MNER 3 No Error Check should check return value of exec 518
 MNER 3 No Error Check should check return value of exec 536
 MNER 3 No Error Check should check @@error after delete 546
 MNER 3 No Error Check should check return value of exec 560
 MNER 3 No Error Check should check return value of exec 572
 MNER 3 No Error Check should check return value of exec 590
 MNER 3 No Error Check should check return value of exec 610
 MNER 3 No Error Check should check return value of exec 614
 MUCO 3 Useless Code Useless Brackets in create proc 15
 MUCO 3 Useless Code Useless Brackets 74
 MUCO 3 Useless Code Useless Brackets 131
 MUCO 3 Useless Code Useless Brackets 143
 MUCO 3 Useless Code Useless Brackets 178
 MUCO 3 Useless Code Useless Brackets 201
 MUCO 3 Useless Code Useless Brackets 206
 MUCO 3 Useless Code Useless Brackets 212
 MUCO 3 Useless Code Useless Brackets 227
 MUCO 3 Useless Code Useless Brackets 230
 MUCO 3 Useless Code Useless Brackets 234
 MUCO 3 Useless Code Useless Brackets 241
 MUCO 3 Useless Code Useless Brackets 257
 MUCO 3 Useless Code Useless Brackets 287
 MUCO 3 Useless Code Useless Brackets 309
 MUCO 3 Useless Code Useless Brackets 337
 MUCO 3 Useless Code Useless Brackets 340
 MUCO 3 Useless Code Useless Brackets 352
 MUCO 3 Useless Code Useless Brackets 353
 MUCO 3 Useless Code Useless Brackets 388
 MUCO 3 Useless Code Useless Brackets 409
 MUCO 3 Useless Code Useless Brackets 415
 MUCO 3 Useless Code Useless Brackets 422
 MUCO 3 Useless Code Useless Brackets 426
 MUCO 3 Useless Code Useless Brackets 438
 MUCO 3 Useless Code Useless Brackets 466
 MUCO 3 Useless Code Useless Brackets 511
 MUCO 3 Useless Code Useless Brackets 528
 MUCO 3 Useless Code Useless Brackets 553
 MUCO 3 Useless Code Useless Brackets 555
 MUCO 3 Useless Code Useless Brackets 601
 MUCO 3 Useless Code Useless Brackets 650
 MUIN 3 Column created using implicit nullability 103
 MUIN 3 Column created using implicit nullability 164
 MUIN 3 Column created using implicit nullability 269
 QAFM 3 Var Assignment from potentially many rows 88
 QAFM 3 Var Assignment from potentially many rows 91
 QAFM 3 Var Assignment from potentially many rows 94
 QAFM 3 Var Assignment from potentially many rows 136
 QAFM 3 Var Assignment from potentially many rows 371
 QAFM 3 Var Assignment from potentially many rows 495
 QAFM 3 Var Assignment from potentially many rows 498
 QCRS 3 Conditional Result Set 563
 QDIS 3 Check correct use of 'select distinct' 115
 QGWO 3 Group by/Distinct/Union without order by 115
 QISO 3 Set isolation level 81
 QJWT 3 Join or Sarg Without Index on temp table 197
 QNAJ 3 Not using ANSI Inner Join 116
 QNAJ 3 Not using ANSI Inner Join 196
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_type, object, attribute, class}
138
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_type, object, attribute, class}
373
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
401
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
532
 QTJ1 3 Table only appears in inner join clause 196
 QTLO 3 Top-Level OR 503
 VNRD 3 Variable is not read @dummy 239
 VUNU 3 Variable is not used @devsize 24
 VUNU 3 Variable is not used @ldev 26
 VUNU 3 Variable is not used @physdev 26
 VUNU 3 Variable is not used @longstr 60
 VUNU 3 Variable is not used @tempcol 61
 CUPD 2 Updatable Cursor Marker (updatable by default) 106
 CUPD 2 Updatable Cursor Marker (updatable by default) 126
 CUPD 2 Updatable Cursor Marker (updatable by default) 172
 CUPD 2 Updatable Cursor Marker (updatable by default) 274
 MDYS 2 Dynamic SQL Marker 157
 MDYS 2 Dynamic SQL Marker 382
 MRST 2 Result Set Marker 563
 MSUB 2 Subquery Marker 324
 MSUB 2 Subquery Marker 362
 MTR1 2 Metrics: Comments Ratio Comments: 41% 15
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 38 = 40dec - 4exi + 2 15
 MTR3 2 Metrics: Query Complexity Complexity: 302 15
 PRED_QUERY_COLLECTION 2 {d=master..sysdatabases, d2=master..sysdevices, u=master..sysusages} 0 115

DEPENDENCIES
PROCS AND TABLES USED
read_writes table tempdb..#dbcc_dblist (1) 
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)  
reads table master..sysattributes (1)  
reads table master..sysusages (1)  
read_writes table tempdb..#dbcc_devlist (1) 
reads table master..spt_values (1)  
calls proc sybsystemprocs..sp_is_valid_user  
   read_writes table tempdb..#t (1) 
   reads table master..sysdatabases (1)  
   reads table master..sysloginroles (1)  
calls proc sybsystemprocs..sp_getmessage  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..sysmessages (1)  
   reads table sybsystemprocs..sysusermessages  
   reads table master..syslanguages (1)  
reads table master..sysdatabases (1)  
read_writes table tempdb..#dbcc_config (1) 
reads table master..sysdevices (1)  
writes table sybsystemprocs..sp_plan_dbccdb_rset_001 
calls proc sybsystemprocs..sp_dbcc_scale_factor