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


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 468
 MEST 4 Empty String will be replaced by Single Space 504
 MEST 4 Empty String will be replaced by Single Space 545
 MEST 4 Empty String will be replaced by Single Space 559
 MEST 4 Empty String will be replaced by Single Space 575
 MEST 4 Empty String will be replaced by Single Space 619
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 MTYP 4 Assignment type mismatch dbname: varchar(30) = varchar(255) 460
 MTYP 4 Assignment type mismatch @dbid1: smallint = int 478
 MTYP 4 Assignment type mismatch @dbid2: smallint = int 481
 MTYP 4 Assignment type mismatch @str1: char(30) = varchar(255) 604
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
85
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
88
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
91
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 114
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 134
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 137
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 170
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 211
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 307
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 318
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 356
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 358
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 359
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 481
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 486
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 515
 QTYP 4 Comparison type mismatch smallint = int 515
 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 122
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause c_dbcc_devlist 168
 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) 167
 MNER 3 No Error Check should check @@error after insert 110
 MNER 3 No Error Check should check @@error after insert 177
 MNER 3 No Error Check should check @@error after update 191
 MNER 3 No Error Check should check @@error after delete 211
 MNER 3 No Error Check should check return value of exec 212
 MNER 3 No Error Check should check @@error after insert 458
 MNER 3 No Error Check should check return value of exec 501
 MNER 3 No Error Check should check return value of exec 519
 MNER 3 No Error Check should check @@error after delete 529
 MNER 3 No Error Check should check return value of exec 543
 MNER 3 No Error Check should check return value of exec 555
 MNER 3 No Error Check should check return value of exec 573
 MNER 3 No Error Check should check return value of exec 593
 MNER 3 No Error Check should check return value of exec 597
 MUCO 3 Useless Code Useless Brackets in create proc 15
 MUCO 3 Useless Code Useless Brackets 70
 MUCO 3 Useless Code Useless Brackets 127
 MUCO 3 Useless Code Useless Brackets 139
 MUCO 3 Useless Code Useless Brackets 174
 MUCO 3 Useless Code Useless Brackets 197
 MUCO 3 Useless Code Useless Brackets 202
 MUCO 3 Useless Code Useless Brackets 208
 MUCO 3 Useless Code Useless Brackets 219
 MUCO 3 Useless Code Useless Brackets 226
 MUCO 3 Useless Code Useless Brackets 241
 MUCO 3 Useless Code Useless Brackets 270
 MUCO 3 Useless Code Useless Brackets 292
 MUCO 3 Useless Code Useless Brackets 320
 MUCO 3 Useless Code Useless Brackets 323
 MUCO 3 Useless Code Useless Brackets 335
 MUCO 3 Useless Code Useless Brackets 336
 MUCO 3 Useless Code Useless Brackets 371
 MUCO 3 Useless Code Useless Brackets 392
 MUCO 3 Useless Code Useless Brackets 398
 MUCO 3 Useless Code Useless Brackets 405
 MUCO 3 Useless Code Useless Brackets 409
 MUCO 3 Useless Code Useless Brackets 421
 MUCO 3 Useless Code Useless Brackets 449
 MUCO 3 Useless Code Useless Brackets 494
 MUCO 3 Useless Code Useless Brackets 511
 MUCO 3 Useless Code Useless Brackets 536
 MUCO 3 Useless Code Useless Brackets 538
 MUCO 3 Useless Code Useless Brackets 584
 MUCO 3 Useless Code Useless Brackets 633
 MUIN 3 Column created using implicit nullability 99
 MUIN 3 Column created using implicit nullability 160
 MUIN 3 Column created using implicit nullability 252
 QAFM 3 Var Assignment from potentially many rows 84
 QAFM 3 Var Assignment from potentially many rows 87
 QAFM 3 Var Assignment from potentially many rows 90
 QAFM 3 Var Assignment from potentially many rows 132
 QAFM 3 Var Assignment from potentially many rows 354
 QAFM 3 Var Assignment from potentially many rows 478
 QAFM 3 Var Assignment from potentially many rows 481
 QCRS 3 Conditional Result Set 546
 QDIS 3 Check correct use of 'select distinct' 111
 QGWO 3 Group by/Distinct/Union without order by 111
 QISO 3 Set isolation level 77
 QJWT 3 Join or Sarg Without Index on temp table 193
 QNAJ 3 Not using ANSI Inner Join 112
 QNAJ 3 Not using ANSI Inner Join 192
 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}
134
 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}
356
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
384
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
515
 QTJ1 3 Table only appears in inner join clause 192
 QTLO 3 Top-Level OR 486
 VNRD 3 Variable is not read @dummy 225
 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 57
 VUNU 3 Variable is not used @tempcol 58
 CUPD 2 Updatable Cursor Marker (updatable by default) 102
 CUPD 2 Updatable Cursor Marker (updatable by default) 122
 CUPD 2 Updatable Cursor Marker (updatable by default) 168
 CUPD 2 Updatable Cursor Marker (updatable by default) 257
 MDYS 2 Dynamic SQL Marker 153
 MDYS 2 Dynamic SQL Marker 365
 MRST 2 Result Set Marker 546
 MSUB 2 Subquery Marker 307
 MSUB 2 Subquery Marker 345
 MTR1 2 Metrics: Comments Ratio Comments: 41% 15
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 35 = 37dec - 4exi + 2 15
 MTR3 2 Metrics: Query Complexity Complexity: 293 15
 PRED_QUERY_COLLECTION 2 {d=master..sysdatabases, d2=master..sysdevices, u=master..sysusages} 0 111

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