DatabaseProcApplicationCreatedLinks
sybsystemprocssp_spaceused  31 Aug 14Defects Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     /*	4.8	1.1	06/14/90	sproc/src/spaceused */
4     
5     /*
6     ** Messages for "sp_spaceused"          17830
7     **
8     ** 17460, "Object must be in the current database." 
9     ** 17461, "Object does not exist in this database."
10    ** 17830, "Object is stored in 'sysprocedures' and
11    ** 	   has no space allocated directly."
12    ** 17831, "Views don't have space allocated."
13    ** 17832, "Not avail."
14    */
15    
16    /*
17    ** IMPORTANT NOTE:
18    ** This stored procedure uses the built-in function object_id() in the
19    ** where clause of a select query. If you intend to change this query
20    ** or use the object_id() or db_id() builtin in this procedure, please read the
21    ** READ.ME file in the $DBMS/generic/sproc directory to ensure that the rules
22    ** pertaining to object-id's and db-id's outlined there, are followed.
23    */
24    
25    create procedure sp_spaceused
26        @objname varchar(317) = null, /* the object we want size on */
27        @list_indices int = 0 /* don't sum all indices, list each */
28    as
29    
30        declare @type smallint /* the object type */
31        declare @msg varchar(1024) /* message output */
32        declare @dbname varchar(255) /* database name */
33        declare @tabname varchar(255) /* table name */
34        declare @length int
35        declare @isarchivedb int /* Is this an archive database? */
36        declare @empty_dpgs int /*
37        ** #empty data pages in hash region
38        ** of Virtually hashed table
39        */
40    
41    
42        if @@trancount = 0
43        begin
44            set chained off
45        end
46    
47        set transaction isolation level 1
48    
49        /* Determine if this is an archive database. */
50        if exists (select * from master.dbo.sysdatabases
51                where dbid = db_id()
52                    and (status3 & 4194304) = 4194304)
53            select @isarchivedb = 1
54        else
55            select @isarchivedb = 0
56    
57        /*
58        **  Check to see that the objname is local.
59        */
60        if @objname is not null
61        begin
62            /*
63            ** Get the dbname and ensure that the object is in the
64            ** current database. Also get the table name - this is later
65            ** needed to see if information is being requested for syslogs.
66            */
67            execute sp_namecrack @objname,
68                @db = @dbname output,
69                @object = @tabname output
70            if @dbname is not NULL
71            begin
72                /*
73                ** 17460, "Object must be in the current database." 
74                */
75                if (@dbname != db_name())
76                begin
77                    raiserror 17460
78                    return (1)
79                end
80            end
81    
82            /*
83            **  Does the object exist?
84            */
85            if not exists (select *
86                    from sysobjects
87                    where id = object_id(@objname))
88            begin
89                /*
90                ** 17461, "Object does not exist in this database."
91                */
92                raiserror 17461
93                return (1)
94            end
95    
96            /* Get the object type */
97            select @type = sysstat & 15
98            from sysobjects
99            where id = object_id(@objname)
100           /*
101           **  See if it's a space object.
102           **  types are:
103           **	0 - trigger
104           **	1 - system table
105           **	2 - view
106           **	3 - user table
107           **	4 - sproc
108           **	6 - default
109           **	7 - rule
110           */
111           if not exists (select *
112                   from sysindexes
113                   where id = object_id(@objname)
114                       and indid < 2)
115           begin
116               if @type not in (1, 2, 3)
117               begin
118                   /*
119                   ** 17830, "Object is stored in 'sysprocedures' and
120                   ** 	   has no space allocated directly."
121                   */
122                   raiserror 17830
123                   return (1)
124               end
125   
126               if @type = 2
127               begin
128                   /*
129                   ** 17831, "Views don't have space allocated."
130                   */
131                   raiserror 17831
132                   return (1)
133               end
134           end
135   
136       end
137   
138       /*
139       **  If @objname is null, then we want summary data.
140       */
141       set nocount on
142       if @objname is null
143       begin
144           declare @slog_res_pgs numeric(20, 9), /* number of reserved pgs. in syslogs */
145               @slog_dpgs numeric(20, 9) /* number of data pages in syslogs */
146   
147           if (@isarchivedb = 0)
148           begin
149               /* This is a normal database. */
150               select distinct database_name = db_name(), database_size =
151                   ltrim(str(sum(size) / (1048576 / d.low), 10, 1)) + " MB"
152               into #spaceused1result
153               from master.dbo.sysusages, master.dbo.spt_values d
154               where dbid = db_id()
155                   and d.number = 1
156                   and d.type = "E"
157                   and vdevno >= 0
158               having dbid = db_id()
159                   and d.number = 1
160                   and d.type = "E"
161                   and vdevno >= 0
162   
163               exec sp_autoformat #spaceused1result
164               drop table #spaceused1result
165           end
166           else
167           begin
168               /* This is an archive database. */
169               declare @scratchdb sysname
170               declare @dbsize int
171               declare @sizestr varchar(128)
172   
173               /*
174               ** The original diskmap is stored in the sysaltusages catalog
175               ** in the scratch database with a location = 4.
176               ** Read the scratch database name from sysattributes first.
177               */
178               select @scratchdb = convert(sysname, char_value)
179               from master.dbo.sysattributes
180               where class = 28
181                   and object_type = "D"
182                   and object = db_id()
183                   and attribute = 0
184   
185               select @sizestr = 'select @dbsize=sum(size) from ' +
186                   @scratchdb + '.dbo.sysaltusages' +
187                   ' where dbid=' + convert(char, db_id()) +
188                   ' and location = 4'
189               exec (@sizestr)
190   
191               select distinct
192                   database_name = db_name(),
193                   original_size =
194                   ltrim(str(@dbsize / (1048576 / d.low), 10, 1))
195                   + " MB",
196                   modified_pages_size =
197                   ltrim(str(sum(size) / (1048576 / d.low), 10, 1))
198                   + " MB",
199                   unused =
200                   ltrim(str(sum(unreservedpgs) / (1048576 / d.low), 10, 1))
201                   + " MB"
202               into #spaceused2result
203               from master.dbo.sysusages, master.dbo.spt_values d
204               where dbid = db_id()
205                   and d.number = 1
206                   and d.type = "E"
207               having dbid = db_id()
208                   and d.number = 1
209                   and d.type = "E"
210   
211               exec sp_autoformat #spaceused2result
212               drop table #spaceused2result
213           end
214   
215           /*
216           ** Obtain the page count for syslogs table. 
217           ** 
218           ** The syslogs system table has only data (no index does exist).
219           ** Built-in functions reserved_pages and data_pages will always 
220           ** return the same value for syslogs.
221           ** This is due to the fact that syslogs pages are allocated an extent
222           ** worth at a time and all log pages in this extent are set as in use.
223           ** This is why we aren't able to determine the amount of unused 
224           ** syslogs pages by simply doing reserved_pages - data_pages.
225           **
226           ** Also note that syslogs table doesn't have OAM pages.  However,
227           ** builtin functions reserved_pages() and data_pages() handle syslogs
228           ** as a special case.
229           */
230           select @slog_res_pgs = convert(numeric(20, 9), reserved_pages(db_id(), 8))
231           select @slog_dpgs = @slog_res_pgs
232   
233           /*
234           ** Obtain the page count for all the objects in the current
235           ** database; except for 'syslogs' (id = 8). Store the results
236           ** in a temp. table (#pgcounts).
237           **
238           ** Note that we first retrieve the needed information from
239           ** sysindexes and we only then apply the OAM builtin system
240           ** functions on that data.  The reason being we want to relax
241           ** keeping the sh_int table lock on sysindexes for the duration
242           ** of the command.
243           */
244           select distinct
245               s.name,
246               s.id,
247               s.indid,
248               res_pgs = 0,
249               low = d.low,
250               dpgs = convert(numeric(20, 9), 0),
251               ipgs = convert(numeric(20, 9), 0),
252               unused = convert(numeric(20, 9), 0)
253           into #pgcounts
254           from sysindexes s, master.dbo.spt_values d
255           where s.id != 8
256               and d.number = 1
257               and d.type = "E"
258           having d.number = 1
259               and d.type = "E"
260           /*
261           ** If the database has one or more Virtually hashed tables, then
262           ** we have to count the empty pages in the hash region of those
263           ** tables. Find out all the Virtually hashed tables, count the
264           ** empty pages and modify the #data pages and #unused pages to be
265           ** reported accordingly.
266           */
267           select distinct attrib = convert(char(30), a.char_value),
268               id = s.id,
269               emptypg_cnt = 0
270           into #vhash_tbls
271           from sysattributes t, master.dbo.sysattributes c,
272               master.dbo.sysattributes a, sysindexes s
273           where t.object_type = "T"
274               and t.object = s.id
275               and c.class = 0 and c.attribute = 0
276               and a.class = 0 and a.attribute = 1
277               and t.class = c.object
278               and t.class = a.object
279               and t.attribute = a.object_info1
280   
281           delete from #vhash_tbls where attrib <> 'hash key factors'
282   
283           update #vhash_tbls set
284               emptypg_cnt = emptypgcnt
285           from systabstats, #vhash_tbls
286           where systabstats.id = #vhash_tbls.id
287   
288           select @empty_dpgs = sum(emptypg_cnt)
289           from #vhash_tbls
290   
291           if (@empty_dpgs is NULL)
292           begin
293               select @empty_dpgs = 0
294           end
295   
296           /* Calculate the reserved pages, data pages, index pages and
297           ** unused pages. Note that we take care of the special case
298           ** of indid = 1, 0 in later steps. For indid = 1 case we need
299           ** to get the data pages and index pages in separate steps.
300           **
301           ** For the clustered index case calculate the data and reserved pages
302           ** by passing in indid of 0 to the builtins. Note, for indid = 1
303           ** the data pages are accounted for in ipgs. 
304           **
305           ** Calculate the unused count for the special case of indid  = 1
306           */
307           update #pgcounts set
308               dpgs =
309               (case
310                   when (indid = 0) then
311                   (convert(numeric(20, 9), data_pages(db_id(), id, indid)))
312                   when (indid = 1) then
313                   (dpgs + convert(numeric(20, 9), data_pages(db_id(), id, 0)))
314                   else
315                       0
316               end),
317               ipgs =
318               (case
319                   when (indid = 1) then
320                   (ipgs + convert(numeric(20, 9), data_pages(db_id(), id, indid)))
321                   when (indid > 1) then
322                   (convert(numeric(20, 9), data_pages(db_id(), id, indid)))
323                   else
324                       0
325               end),
326               res_pgs =
327               (case
328                   when (indid = 1) then
329                   (res_pgs + reserved_pages(db_id(), id, 0)
330                   + reserved_pages(db_id(), id, indid))
331                   else
332                       (reserved_pages(db_id(), id, indid))
333               end)
334   
335           /* Update unused page counts */
336           update #pgcounts set
337               unused =
338               (case
339                   when (indid = 0) then
340                   (convert(numeric(20, 9), (res_pgs - dpgs)))
341                   when (indid = 1) then
342                   (convert(numeric(20, 9), (res_pgs - dpgs - ipgs)))
343                   else
344                       (convert(numeric(20, 9), (res_pgs - ipgs)))
345               end)
346   
347           /*
348           ** Compute the summary results by adding page counts from
349           ** individual data objects. Add to the count the count of 
350           ** pages for 'syslogs'.  Convert the total pages to space
351           ** used in Kilo bytes.
352           */
353           select distinct reserved = convert(char(15), convert(varchar(11),
354               convert(numeric(11, 0), (sum(res_pgs) + @slog_res_pgs) *
355               (low / 1024))) + " " + "KB"),
356               data = convert(char(15), convert(varchar(11),
357               convert(numeric(11, 0), (sum(dpgs) + @slog_dpgs -
358               @empty_dpgs) * (low / 1024))) + " " + "KB"),
359               index_size = convert(char(15), convert(varchar(11),
360               convert(numeric(11, 0), sum(ipgs) * (low / 1024)))
361               + " " + "KB"),
362               unused = convert(char(15), convert(varchar(11),
363               convert(numeric(11, 0), (sum(unused) + @empty_dpgs) *
364               (low / 1024))) + " " + "KB")
365           into #fmtpgcnts
366           from #pgcounts
367   
368           exec sp_autoformat #fmtpgcnts
369           drop table #fmtpgcnts
370       end
371   
372       /*
373       **  We want a particular object.
374       */
375       else
376       begin
377           if (@tabname = "syslogs") /* syslogs */
378           begin
379               declare @free_pages int, /* log free space in pages */
380                   @clr_pages int, /* log space reserved for CLRs */
381                   @total_pages int, /* total allocatable log space */
382                   @used_pages int, /* allocated log space */
383                   @ismixedlog int /* mixed log & data database ? */
384   
385               select @ismixedlog = status2 & 32768
386               from master.dbo.sysdatabases where dbid = db_id()
387   
388               select @clr_pages = lct_admin("reserved_for_rollbacks",
389                       db_id())
390               select @free_pages = lct_admin("logsegment_freepages", db_id())
391                   - @clr_pages
392   
393               select @total_pages = sum(u.size)
394               from master.dbo.sysusages u
395               where u.segmap & 4 = 4
396                   and u.dbid = db_id()
397   
398               if (@ismixedlog = 32768)
399               begin
400                   /* 
401                   ** For a mixed log and data database, we cannot
402                   ** deduce the log used space from the total space
403                   ** as it is mixed with data. So we take the expensive
404                   ** way by scanning syslogs.
405                   */
406                   select @used_pages = lct_admin("num_logpages", db_id())
407   
408                   /* Account allocation pages as used pages */
409                   select @used_pages = @used_pages + (@total_pages / 256)
410               end
411               else
412               begin
413                   /* Dedicated log database */
414                   select @used_pages = @total_pages - @free_pages
415                       - @clr_pages
416               end
417   
418               select name = convert(char(15), @tabname),
419                   total_pages = convert(char(15), @total_pages),
420                   free_pages = convert(char(15), @free_pages),
421                   used_pages = convert(char(15), @used_pages),
422                   reserved_pages = convert(char(15), @clr_pages)
423           end
424           else
425           begin
426               /*
427               ** Obtain the page count for the target object in the current
428               ** database and store them in the temp table #pagecounts.
429               **
430               ** Note that we first retrieve the needed information from
431               ** sysindexes and we only then apply the OAM builtin system
432               ** functions on that data.  The reason being we want to relax
433               ** keeping the sh_int table lock on sysindexes for the duration
434               ** of the command.
435               */
436               select name = o.name,
437                   tabid = i.id,
438                   iname = i.name,
439                   indid = i.indid,
440                   low = d.low,
441                   rowtotal = convert(numeric(18, 0), 0),
442                   reserved = convert(numeric(20, 9), 0),
443                   data = convert(numeric(20, 9), 0),
444                   index_size = convert(numeric(20, 9), 0),
445                   unused = convert(numeric(20, 9), 0)
446               into #pagecounts
447               from sysobjects o, sysindexes i, master.dbo.spt_values d
448               where i.id = object_id(@objname)
449                   and o.id = i.id
450                   and d.number = 1
451                   and d.type = "E"
452   
453               /* perform the row counts */
454               update #pagecounts
455               set rowtotal = row_count(db_id(), tabid)
456               where indid <= 1
457   
458               /* calculate the counts for indid > 1
459               ** case of indid = 1, 0 are special cases done later
460               */
461               update #pagecounts set
462                   reserved = convert(numeric(20, 9),
463                   reserved_pages(db_id(), tabid, indid)),
464                   index_size = convert(numeric(20, 9),
465                   data_pages(db_id(), tabid, indid))
466               where indid > 1
467   
468               /* calculate for case where indid = 0 */
469               update #pagecounts set
470                   reserved = convert(numeric(20, 9),
471                   reserved_pages(db_id(), tabid, indid)),
472                   data = convert(numeric(20, 9),
473                   data_pages(db_id(), tabid, indid))
474               where indid = 0
475   
476               /* handle the case where indid = 1, since we need
477               ** to take care of the data and index pages. 
478               */
479               update #pagecounts set
480                   reserved = convert(numeric(20, 9),
481                   reserved_pages(db_id(), tabid, 0))
482                   + convert(numeric(20, 9),
483                   reserved_pages(db_id(), tabid, indid)),
484                   index_size = convert(numeric(20, 9),
485                   data_pages(db_id(), tabid, indid)),
486                   data = convert(numeric(20, 9),
487                   data_pages(db_id(), tabid, 0))
488               where indid = 1
489   
490               /* calculate the unused count for all the indexes & data.*/
491               update #pagecounts set
492                   unused =
493                   (case
494                       when (indid = 0) then
495                       (convert(numeric(20, 9),
496                       (reserved - data)))
497                       when (indid = 1) then
498                       (convert(numeric(20, 9),
499                       reserved - data - index_size))
500                       else
501                           (convert(numeric(20, 9),
502                           reserved - index_size))
503                   end)
504   
505   
506               if (@list_indices = 1)
507               begin
508                   select index_name = iname,
509                       size = convert(char(10), convert(varchar(11),
510                       convert(numeric(11, 0),
511                       index_size / 1024 *
512                       low)) + " " + "KB"),
513                       reserved = convert(char(10),
514                       convert(varchar(11),
515                       convert(numeric(11, 0),
516                       reserved / 1024 *
517                       low)) + " " + "KB"),
518                       unused = convert(char(10), convert(varchar(11),
519                       convert(numeric(11, 0), unused / 1024 *
520                       low)) + " " + "KB")
521                   into #formatpgcounts
522                   from #pagecounts
523                   where indid > 0
524   
525                   exec sp_autoformat #formatpgcounts
526                   drop table #formatpgcounts
527               end
528   
529               /*
530               ** Check whether the table is Virtually hashed. For Virtually
531               ** Hashed tables, we maintain the number of empty pages in
532               ** systabstats. Compute the #data pages and #unused pages
533               ** based on that value.
534               */
535               if (exists (select convert(char(30), a.char_value)
536                           from sysattributes t, master.dbo.sysattributes c,
537                               master.dbo.sysattributes a
538                           where t.object_type = "T"
539                               and t.object = object_id(@objname)
540                               and c.class = 0 and c.attribute = 0
541                               and a.class = 0 and a.attribute = 1
542                               and t.class = c.object
543                               and t.class = a.object
544                               and t.attribute = a.object_info1
545                               and a.char_value = 'hash key factors'))
546               begin
547                   select @empty_dpgs = emptypgcnt
548                   from systabstats where id = object_id(@objname)
549               end
550               else
551               begin
552                   select @empty_dpgs = 0
553               end
554   
555               select distinct name,
556                   rowtotal = convert(char(15), sum(rowtotal)),
557                   reserved = convert(char(15), convert(varchar(11),
558                   convert(numeric(11, 0), sum(reserved) *
559                   (low / 1024))) + " " + "KB"),
560                   data = convert(char(15), convert(varchar(11),
561                   convert(numeric(11, 0), (sum(data) - @empty_dpgs) * (low / 1024)))
562                   + " " + "KB"),
563                   index_size = convert(char(15), convert(varchar(11),
564                   convert(numeric(11, 0), sum(index_size) *
565                   (low / 1024))) + " " + "KB"),
566                   unused = convert(char(15), convert(varchar(11),
567                   convert(numeric(11, 0), (sum(unused) + @empty_dpgs) *
568                   (low / 1024))) + " " + "KB")
569               into #fmtpgcounts
570               from #pagecounts
571   
572               exec sp_autoformat #fmtpgcounts
573               drop table #fmtpgcounts
574           end
575       end
576       return (0)
577   


exec sp_procxmode 'sp_spaceused', 'AnyMode'
go

Grant Execute on sp_spaceused to public
go
RESULT SETS
sp_spaceused_rset_001

DEFECTS
 QBGB 6 Bad group by : Query requires a 'group by' clause 150
 QBGB 6 Bad group by : Query requires a 'group by' clause 192
 QBGB 6 Bad group by : Query requires a 'group by' clause 353
 QBGB 6 Bad group by : Query requires a 'group by' clause 555
 QCAR 6 Cartesian product between tables master..sysusages and [master..spt_values d] 153
 QCAR 6 Cartesian product between tables master..sysusages and [master..spt_values d] 203
 QCAR 6 Cartesian product between tables sybsystemprocs..sysindexes s and [master..spt_values d] 254
 QCAR 6 Cartesian product between tables sybsystemprocs..sysobjects o and [master..spt_values d] 447
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 MINU 4 Unique Index with nullable columns sybsystemprocs..sysattributes sybsystemprocs..sysattributes
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 163
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 211
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 368
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 525
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 572
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object}
Uncovered: [class, attribute, object_info1, object_info2, object_info3, object_cinfo]
274
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object}
Uncovered: [object_type, object_info1, object_info2, object_info3, object_cinfo]
277
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object, object_info1}
Uncovered: [object_type, object_info2, object_info3, object_cinfo]
278
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object}
Uncovered: [object_type, object_info1, object_info2, object_info3, object_cinfo]
542
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object, object_info1}
Uncovered: [object_type, object_info2, object_info3, object_cinfo]
543
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 51
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 114
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 154
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 158
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 180
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 183
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 204
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 207
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 275
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 276
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 277
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 278
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 279
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 310
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 312
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 319
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 321
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 328
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 339
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 341
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 386
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 396
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 456
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 466
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 474
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 488
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 494
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 497
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 523
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 540
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 541
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 542
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 543
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 544
 TNOU 4 Table with no unique index master..spt_values master..spt_values
 VRUN 4 Variable is read and not initialized @dbsize 194
 MDYN 3 Proc uses Dynamic SQL but is not flagged with Dynamic Ownership Chain 25
 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..sysusages  
 MGTP 3 Grant to public sybsystemprocs..sp_spaceused  
 MGTP 3 Grant to public sybsystemprocs..sysattributes  
 MGTP 3 Grant to public sybsystemprocs..sysindexes  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MGTP 3 Grant to public sybsystemprocs..systabstats  
 MNER 3 No Error Check should check return value of exec 67
 MNER 3 No Error Check should check @@error after select into 150
 MNER 3 No Error Check should check return value of exec 163
 MNER 3 No Error Check should check @@error after select into 191
 MNER 3 No Error Check should check return value of exec 211
 MNER 3 No Error Check should check @@error after select into 244
 MNER 3 No Error Check should check @@error after select into 267
 MNER 3 No Error Check should check @@error after delete 281
 MNER 3 No Error Check should check @@error after update 283
 MNER 3 No Error Check should check @@error after update 307
 MNER 3 No Error Check should check @@error after update 336
 MNER 3 No Error Check should check @@error after select into 353
 MNER 3 No Error Check should check return value of exec 368
 MNER 3 No Error Check should check @@error after select into 436
 MNER 3 No Error Check should check @@error after update 454
 MNER 3 No Error Check should check @@error after update 461
 MNER 3 No Error Check should check @@error after update 469
 MNER 3 No Error Check should check @@error after update 479
 MNER 3 No Error Check should check @@error after update 491
 MNER 3 No Error Check should check @@error after select into 508
 MNER 3 No Error Check should check return value of exec 525
 MNER 3 No Error Check should check @@error after select into 555
 MNER 3 No Error Check should check return value of exec 572
 MUCO 3 Useless Code Useless Brackets 75
 MUCO 3 Useless Code Useless Brackets 78
 MUCO 3 Useless Code Useless Brackets 93
 MUCO 3 Useless Code Useless Brackets 123
 MUCO 3 Useless Code Useless Brackets 132
 MUCO 3 Useless Code Useless Brackets 147
 MUCO 3 Useless Code Useless Brackets 291
 MUCO 3 Useless Code Useless Brackets 309
 MUCO 3 Useless Code Useless Brackets 310
 MUCO 3 Useless Code Useless Brackets 311
 MUCO 3 Useless Code Useless Brackets 312
 MUCO 3 Useless Code Useless Brackets 318
 MUCO 3 Useless Code Useless Brackets 319
 MUCO 3 Useless Code Useless Brackets 321
 MUCO 3 Useless Code Useless Brackets 322
 MUCO 3 Useless Code Useless Brackets 327
 MUCO 3 Useless Code Useless Brackets 328
 MUCO 3 Useless Code Useless Brackets 332
 MUCO 3 Useless Code Useless Brackets 338
 MUCO 3 Useless Code Useless Brackets 339
 MUCO 3 Useless Code Useless Brackets 340
 MUCO 3 Useless Code Useless Brackets 341
 MUCO 3 Useless Code Useless Brackets 342
 MUCO 3 Useless Code Useless Brackets 344
 MUCO 3 Useless Code Useless Brackets 377
 MUCO 3 Useless Code Useless Brackets 398
 MUCO 3 Useless Code Useless Brackets 409
 MUCO 3 Useless Code Useless Brackets 493
 MUCO 3 Useless Code Useless Brackets 494
 MUCO 3 Useless Code Useless Brackets 495
 MUCO 3 Useless Code Useless Brackets 497
 MUCO 3 Useless Code Useless Brackets 498
 MUCO 3 Useless Code Useless Brackets 501
 MUCO 3 Useless Code Useless Brackets 506
 MUCO 3 Useless Code Useless Brackets 535
 MUCO 3 Useless Code Useless Brackets 576
 QAFM 3 Var Assignment from potentially many rows 178
 QAFM 3 Var Assignment from potentially many rows 547
 QCRS 3 Conditional Result Set 418
 QCTC 3 Conditional Table Creation 150
 QCTC 3 Conditional Table Creation 191
 QCTC 3 Conditional Table Creation 244
 QCTC 3 Conditional Table Creation 267
 QCTC 3 Conditional Table Creation 353
 QCTC 3 Conditional Table Creation 436
 QCTC 3 Conditional Table Creation 508
 QCTC 3 Conditional Table Creation 555
 QDIS 3 Check correct use of 'select distinct' 150
 QDIS 3 Check correct use of 'select distinct' 191
 QDIS 3 Check correct use of 'select distinct' 244
 QDIS 3 Check correct use of 'select distinct' 267
 QGWO 3 Group by/Distinct/Union without order by 150
 QGWO 3 Group by/Distinct/Union without order by 191
 QGWO 3 Group by/Distinct/Union without order by 244
 QGWO 3 Group by/Distinct/Union without order by 267
 QGWO 3 Group by/Distinct/Union without order by 353
 QGWO 3 Group by/Distinct/Union without order by 555
 QISO 3 Set isolation level 47
 QJWT 3 Join or Sarg Without Index on temp table 286
 QNAJ 3 Not using ANSI Inner Join 153
 QNAJ 3 Not using ANSI Inner Join 203
 QNAJ 3 Not using ANSI Inner Join 254
 QNAJ 3 Not using ANSI Inner Join 271
 QNAJ 3 Not using ANSI Inner Join 285
 QNAJ 3 Not using ANSI Inner Join 447
 QNAJ 3 Not using ANSI Inner Join 536
 QNUA 3 Should use Alias: Column size should use alias sysusages 151
 QNUA 3 Should use Alias: Table master..sysusages 153
 QNUA 3 Should use Alias: Column dbid should use alias sysusages 154
 QNUA 3 Should use Alias: Column vdevno should use alias sysusages 157
 QNUA 3 Should use Alias: Column dbid should use alias sysusages 158
 QNUA 3 Should use Alias: Column vdevno should use alias sysusages 161
 QNUA 3 Should use Alias: Column size should use alias sysusages 197
 QNUA 3 Should use Alias: Column unreservedpgs should use alias sysusages 200
 QNUA 3 Should use Alias: Table master..sysusages 203
 QNUA 3 Should use Alias: Column dbid should use alias sysusages 204
 QNUA 3 Should use Alias: Column dbid should use alias sysusages 207
 QNUA 3 Should use Alias: Column emptypgcnt should use alias systabstats 284
 QNUA 3 Should use Alias: Table sybsystemprocs..systabstats 285
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
154
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
158
 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}
180
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
204
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
207
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysindexes.csysindexes unique clustered
(id, indid)
Intersection: {id}
255
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: sysindexes.csysindexes unique clustered
(id, indid)
Intersection: {id}
Uncovered: [indid]
274
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {class}
Uncovered: [attribute, object, object_info1, object_info2, object_info3, object_cinfo]
277
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {class, attribute}
Uncovered: [object, object_info1, object_info2, object_info3, object_cinfo]
278
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
395
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysindexes.csysindexes unique clustered
(id, indid)
Intersection: {id}
448
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {class}
Uncovered: [attribute, object_info1, object_info2, object_info3, object_cinfo]
542
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {class, attribute}
Uncovered: [object_info1, object_info2, object_info3, object_cinfo]
543
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: systabstats.csystabstats unique clustered
(id, indid, partitionid)
Intersection: {id}
548
 VUNU 3 Variable is not used @msg 31
 VUNU 3 Variable is not used @length 34
 MDYS 2 Dynamic SQL Marker 189
 MRST 2 Result Set Marker 418
 MSUB 2 Subquery Marker 50
 MSUB 2 Subquery Marker 85
 MSUB 2 Subquery Marker 111
 MSUB 2 Subquery Marker 535
 MTR1 2 Metrics: Comments Ratio Comments: 34% 25
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 52 = 55dec - 5exi + 2 25
 MTR3 2 Metrics: Query Complexity Complexity: 224 25
 PRED_QUERY_COLLECTION 2 {a=master..sysattributes, a2=sybsystemprocs..sysattributes, a3=master..sysattributes, i=sybsystemprocs..sysindexes} 0 267
 PRED_QUERY_COLLECTION 2 {i=sybsystemprocs..sysindexes, o=sybsystemprocs..sysobjects} 0 436
 PRED_QUERY_COLLECTION 2 {a=sybsystemprocs..sysattributes, a2=master..sysattributes, a3=master..sysattributes} 0 535

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..sysindexes  
writes table tempdb..#spaceused2result (1) 
writes table tempdb..#formatpgcounts (1) 
writes table tempdb..#fmtpgcnts (1) 
writes table tempdb..#fmtpgcounts (1) 
read_writes table tempdb..#pgcounts (1) 
read_writes table tempdb..#vhash_tbls (1) 
read_writes table tempdb..#pagecounts (1) 
reads table master..sysattributes (1)  
reads table sybsystemprocs..systabstats  
writes table tempdb..#spaceused1result (1) 
reads table master..spt_values (1)  
calls proc sybsystemprocs..sp_namecrack  
reads table master..sysusages (1)  
reads table sybsystemprocs..sysattributes  
reads table sybsystemprocs..sysobjects  
reads table master..sysdatabases (1)  
calls proc sybsystemprocs..sp_autoformat  
   reads table master..systypes (1)  
   reads table master..syscolumns (1)  
   reads table tempdb..syscolumns (1)  
   calls proc sybsystemprocs..sp_autoformat  
   read_writes table tempdb..#colinfo_af (1) 
   calls proc sybsystemprocs..sp_namecrack  
   reads table tempdb..systypes (1)