DatabaseProcApplicationCreatedLinks
sybsystemprocssp_spaceused  14 déc. 14Defects Propagation Dependencies

1     
2     /*
3     ** IMPORTANT NOTE:
4     ** This stored procedure uses the built-in function object_id() in the
5     ** where clause of a select query. If you intend to change this query
6     ** or use the object_id() or db_id() builtin in this procedure, please read the
7     ** READ.ME file in the $DBMS/generic/sproc directory to ensure that the rules
8     ** pertaining to object-id's and db-id's outlined there, are followed.
9     **
10    ** Debugging flags (@dbgflags below):
11    **
12    ** Status bit to turn ON different kinds of debugging.
13    **
14    ** 01	0x00000001	- Display timing metrics for individual pieces
15    ** 02	0x00000002	- Display intermediate results generated
16    ** 04	0x00000004	- Trace progress of built-in on server-side
17    {
18    */
19    
20    create or replace procedure sp_spaceused(
21        @objname varchar(317) = null /* the object we want size on */
22        , @list_indices int = 0 /* don't sum all indices, list each */
23        , @dbgflags int = 0 /* debugging flags */
24    ) as
25        begin
26    
27            declare @type smallint /* the object type */
28                , @msg varchar(1024) /* message output */
29                , @dbname varchar(30) /* database name from @objname */
30                , @db_name varchar(30) /* database name from built-in */
31                , @dbid smallint
32                , @objid int
33                , @tabname varchar(255) /* table name */
34                , @length int
35                , @isarchivedb tinyint /* Is this an archive database? */
36                , @empty_vhash_pgs bigint /* #empty data pages in hash region
37                ** of Virtually hashed table
38                */
39                , @numpgsmb int /* # of logical pages per MB */
40                , @pgsize_KB int /* Logical page size in KB */
41                , @spaceusage varchar(1024) /* output from built-in of the
42                ** same name
43                */
44                , @reserved_pgs bigint
45                , @used_pgs bigint /* data + index + OAM pages */
46                , @data_pgs bigint
47                , @index_pgs bigint /* data pages for indid >= 1*/
48                , @unused_pgs bigint
49                , @dataonly_pgs bigint /* Not including index pages */
50                , @rowcount bigint
51    
52                -- Boolean debugging flags
53                , @dbg_timing tinyint
54                , @dbg_show_interim_results tinyint
55                , @dbg_trace_progress tinyint
56    
57                , @we_set_TF2502_ON tinyint
58                , @we_set_TF3604_ON tinyint
59    
60                , @sysstat4 tinyint
61                , @tab_volatile tinyint
62    
63            /* Elapsed time related counters */
64            declare @time_start bigtime -- of start of sproc execution
65                , @time_end bigtime
66    
67            if @@trancount = 0
68            begin
69                set chained off
70            end
71    
72            set transaction isolation level 1
73    
74            -- Convert MB to # of logical pages using encoding of @@maxpagesize of
75            -- current installation in spt_values.
76            --
77            select @numpgsmb = (1048576. / d.low) -- # of pages per MB
78                , @pgsize_KB = (d.low / 1024) -- logical page size in KB
79            from master.dbo.spt_values d
80            where d.number = 1
81                and d.type = "E"
82    
83            set @dbid = db_id()
84    
85            -- Set debugging booleans, if any requested
86            --
87            set @dbg_timing = 0
88                , @dbg_show_interim_results = 0
89                , @dbg_trace_progress = 0
90            if ((@dbgflags IS NOT NULL) and (@dbgflags > 0))
91            begin
92                if ((@dbgflags & 0x1) != 0)
93                    set @dbg_timing = 1
94    
95                if ((@dbgflags & 0x2) != 0)
96                    set @dbg_show_interim_results = 1
97    
98                if ((@dbgflags & 0x4) != 0)
99                    set @dbg_trace_progress = 1
100           end
101   
102           /* Determine if this is an archive database. */
103           if exists (select * from master.dbo.sysdatabases
104                   where dbid = @dbid
105                       and (status3 & 4194304) = 4194304)
106               select @isarchivedb = 1
107           else
108               select @isarchivedb = 0
109   
110           set @db_name = db_name()
111               , @we_set_TF2502_ON = 0
112               , @we_set_TF3604_ON = 0
113   
114           /*
115           **  Check to see that the objname is local.
116           */
117           if @objname IS NOT NULL
118           begin -- {
119   
120               /*
121               ** Get the dbname and ensure that the object is in the
122               ** current database. Also get the table name - this is later
123               ** needed to see if information is being requested for syslogs.
124               */
125               execute sp_namecrack @objname,
126                   @db = @dbname output,
127                   @object = @tabname output
128               if @dbname is not NULL
129               begin
130                   /*
131                   ** 17460, "Object must be in the current database." 
132                   */
133                   if (@dbname != @db_name)
134                   begin
135                       raiserror 17460
136                       return (1)
137                   end
138               end
139   
140               /*
141               ** Does the object exist? The following check ensures that
142               ** @objname will name a single object. No wildcards like 't1%' are
143               ** permitted in this interface.
144               */
145               set @objid = object_id(@objname)
146               if (@objid IS NULL)
147               begin
148                   /*
149                   ** 17461, "Object does not exist in this database."
150                   */
151                   raiserror 17461
152                   return (1)
153               end
154   
155               /* Get the object type and syssta4 */
156               select @type = (sysstat & 15), @sysstat4 = sysstat4
157               from sysobjects
158               where id = @objid
159   
160               /*
161               **  See if it's an object consuming space.
162               **  Types are:
163               **	0 - trigger
164               **	1 - system table
165               **	2 - view
166               **	3 - user table
167               **	4 - sproc
168               **	6 - default
169               **	7 - rule
170               */
171               if not exists (select *
172                       from sysindexes
173                       where id = @objid
174                           and indid < 2)
175               begin
176                   if @type not in (1, 2, 3)
177                   begin
178                       /*
179                       ** 17830, "Object is stored in 'sysprocedures' and
180                       ** 	   has no space allocated directly."
181                       */
182                       raiserror 17830
183                       return (1)
184                   end
185   
186                   if @type = 2
187                   begin
188                       /*
189                       ** 17831, "Views don't have space allocated."
190                       */
191                       raiserror 17831
192                       return (1)
193                   end
194               end
195           end -- } @objname IS NOT NULL
196   
197           if (@dbg_timing != 0)
198               set @time_start = current_bigtime()
199   
200           /*
201           **  If @objname is null, then we want summary data.
202           */
203           set nocount on
204           if @objname is null
205           begin -- {
206   
207               declare @sl_total_pages bigint -- total space in syslogs
208                   , @sl_free_pages bigint -- free space in syslogs
209                   , @sl_used_pages bigint -- used space in syslogs
210                   , @sl_used_pages_wo_APs -- used space w/o including
211                   bigint -- allocation pages in syslogs
212                   , @sl_clr_pages bigint -- space set asided temporarily
213                   -- for rollbacks.
214   
215                   -- @variables reported from the sproc output, in # of pages
216                   , @reserved_space_in_db bigint
217                   , @data_space_in_db bigint
218                   , @index_space_in_db bigint
219                   , @unused_space_in_db bigint
220   
221                   -- Timing metrics collected in the for-entire-db block
222                   --
223                   , @time_before_syslogs bigtime
224                   , @time_after_syslogs bigtime
225   
226                   , @time_after_pgcounts_allobjs bigtime
227   
228                   , @time_after_caching_spusage bigtime
229                   , @time_after_parsing_spusage bigtime
230   
231               if (@isarchivedb = 0)
232               begin
233                   /* This is a normal database. Get the total size of the
234                   ** db by looking at the disk pieces in sysusages.
235                   */
236                   select database_name = @db_name
237                       , database_size =
238                       ltrim(str(sum(size) / @numpgsmb, 10, 1)) + " MB"
239                   into #spaceused1result
240                   from master.dbo.sysusages u
241                   where u.dbid = @dbid
242                       and u.vdevno >= 0
243   
244                   exec sp_autoformat #spaceused1result
245                   drop table #spaceused1result
246               end
247               else
248               begin
249                   /* This is an archive database. */
250                   declare @scratchdb sysname
251                   declare @dbsize bigint
252                   declare @sizestr varchar(128)
253   
254                   /*
255                   ** The original diskmap is stored in the sysaltusages catalog
256                   ** in the scratch database with a location = 4.
257                   ** Read the scratch database name from sysattributes first.
258                   */
259                   select @scratchdb = convert(sysname, char_value)
260                   from master.dbo.sysattributes
261                   where class = 28
262                       and object_type = "D"
263                       and object = @dbid
264                       and attribute = 0
265   
266                   select @sizestr = 'select @dbsize=sum(size) from ' +
267                       @scratchdb + '.dbo.sysaltusages' +
268                       ' where dbid=' + convert(char, @dbid) +
269                       ' and location = 4'
270                   exec (@sizestr)
271   
272                   select database_name = @db_name,
273                       original_size =
274                       ltrim(str(@dbsize / @numpgsmb, 10, 1))
275                       + " MB",
276                       modified_pages_size =
277                       ltrim(str(sum(size) / @numpgsmb, 10, 1))
278                       + " MB",
279                       unused =
280                       ltrim(str(sum(unreservedpgs) / @numpgsmb, 10, 1))
281                       + " MB"
282                   into #spaceused2result
283                   from master.dbo.sysusages
284                   where dbid = @dbid
285   
286                   exec sp_autoformat #spaceused2result
287                   drop table #spaceused2result
288               end
289   
290               if (@dbg_timing != 0)
291                   set @time_before_syslogs = current_bigtime()
292   
293               -- Turn on TF that will trace progress on the server-side
294               if (@dbg_trace_progress != 0)
295               begin
296                   -- Cache the relevant rows for the TFs of interest so that
297                   -- we don't grab the fake table data repeatedly.
298                   --
299                   /* Adaptive Server has expanded all '*' elements in the following statement */ select [master].dbo.sysoptions.spid, [master].dbo.sysoptions.name, [master].dbo.sysoptions.category, [master].dbo.sysoptions.currentsetting, [master].dbo.sysoptions.defaultsetting, [master].dbo.sysoptions.scope, [master].dbo.sysoptions.number into #sysoptions
300                   from master.dbo.sysoptions
301                   where number in (2502, 3604)
302   
303                   -- First check if the TF is ON, either locally or
304                   -- server-wide. Turn it ON only if it's not already ON.
305                   --
306                   if (not exists (select * from #sysoptions
307                               where number = 2502
308                                   and category = 'Switch'
309                                   and currentsetting = '1'))
310                   begin
311                       set switch ON 2502 with override, no_info
312                       if (@@error = 0)
313                       begin
314                           set @we_set_TF2502_ON = 1
315   
316                           -- Turn ON tracing output to the client
317                           if (not exists (select * from #sysoptions
318                                       where number = 3604
319                                           and category = 'Switch'
320                                           and currentsetting = '1'))
321                           begin
322                               set switch ON print_output_to_client
323                               with override, no_info
324   
325                               if (@@error = 0)
326                                   set @we_set_TF3604_ON = 1
327                           end
328                       end
329                   end
330               end
331   
332               /*
333               ** Obtain the page count for syslogs table. 
334               ** 
335               ** The syslogs system table has only data (no index does exist).
336               ** Built-in functions reserved_pages and data_pages will always 
337               ** return the same value for syslogs.
338               ** This is due to the fact that syslogs pages are allocated an extent
339               ** worth at a time and all log pages in this extent are set as in use.
340               ** This is why we aren't able to determine the amount of unused 
341               ** syslogs pages by simply doing reserved_pages - data_pages.
342               ** This common sub-proc will give us more than what we want, but
343               ** that should not be a perf overhead.
344               */
345               exec sp_spaceused_syslogs @sl_total_pages output
346                   , @sl_free_pages output
347                   , @sl_used_pages output
348                   , @sl_used_pages_wo_APs output
349                   , @sl_clr_pages output
350   
351               if (@dbg_timing != 0)
352                   set @time_after_syslogs = current_bigtime()
353   
354               /*
355               ** Obtain the page count for all the objects in the current
356               ** database except syslogs. (Syslogs space has been gathered just
357               ** previously.) The built-in's output format conforms to the output
358               ** fields expected by legacy versions of this procedure. In particular,
359               ** the count of pages allocated as index pages is also reported, and
360               ** this goes into reporting the index size output field from this
361               ** procedure. Extract the space usage in a one-line varchar output and
362               ** then parse that to extract out individual fields.
363               */
364   
365               /* RESOLVE: Investigate if this note is still relevant from angle
366               **	of avoiding deadlocks.
367               **
368               ** Note that we first retrieve the needed information from
369               ** sysindexes and we only then apply the OAM builtin system
370               ** functions on that data.  The reason being we want to relax
371               ** keeping the sh_int table lock on sysindexes for the duration
372               ** of the command.
373               */
374               select @spaceusage = spaceusage(@dbid)
375   
376               if (@dbg_timing != 0)
377               begin
378                   set @time_after_caching_spusage = current_bigtime()
379               end
380   
381               if (@dbg_show_interim_results != 0)
382               begin
383                   print " "
384                   print "%1!", @spaceusage
385                   print "Syslogs: total_pages=%1! free_pages=%2! used_pages=%3! sl_used_pages_wo_APs=%4! clr_pages=%5!"
386                       , @sl_total_pages
387                       , @sl_free_pages
388                       , @sl_used_pages
389                       , @sl_used_pages_wo_APs
390                       , @sl_clr_pages
391               end
392   
393               -- Turn OFF server-side TF as we are done with the heavy data
394               -- collection phase of the work.
395               --
396               if (@we_set_TF2502_ON = 1)
397               begin
398                   set switch OFF 2502 with no_info
399   
400                   if (@we_set_TF3604_ON = 1)
401                       set switch OFF print_output_to_client with no_info
402               end
403   
404               -- Extract the reserved pages, data pages, index pages and
405               -- unused pages. Note the built-in has already done the required
406               -- aggregation of page counts for indid=1 as a special-case.
407               --
408               set @reserved_pgs = dbo.sp_f_getuint(@spaceusage, "reserved pages",
409                       default, default)
410                   , @data_pgs = dbo.sp_f_getuint(@spaceusage, "data pages",
411                       default, default)
412                   , @index_pgs = dbo.sp_f_getuint(@spaceusage, "index pages",
413                       default, default)
414                   , @used_pgs = dbo.sp_f_getuint(@spaceusage, "used pages",
415                       default, default)
416   
417               -- Compute the unused pages count. This is the # of pages left out
418               -- of the reserved pages after space has been used by the data, index
419               -- and OAM pages.
420               --
421               set @unused_pgs = (@reserved_pgs - @used_pgs)
422   
423                   -- This is the expected output from this sproc; i.e. 'data' field
424                   -- only reports the space used by data (non-index) pages.
425                   --
426                   , @dataonly_pgs = (@data_pgs - @index_pgs)
427   
428               if (@dbg_timing != 0)
429               begin
430                   set @time_after_parsing_spusage = current_bigtime()
431               end
432   
433               /*
434               ** If the database has one or more Virtually hashed tables, then
435               ** we have to count the empty pages in the hash region of those
436               ** tables. Find out all the Virtually hashed tables, count the
437               ** empty pages and modify the #data pages and #unused pages to be
438               ** reported accordingly. Count(*) is 1st checked to determine if any
439               ** virtually hashed tables even exist, and only then to create the
440               ** #vhash_tbls table. Otherwise, in the common case where such tables
441               ** do not exist, we needlessly generate a #table which slows down the
442               ** performance a tad bit.
443               */
444               set @empty_vhash_pgs = 0
445               if ((select count(*) from sysobjects o
446                           where o.type = "U"
447                               and (o.sysstat2 & 0x80) = 0x80) > 0)
448               begin
449                   select distinct attrib = convert(char(30), a.char_value),
450                       id = s.id,
451                       emptypg_cnt = convert(unsigned int, 0)
452                   into #vhash_tbls
453                   from sysattributes t, master.dbo.sysattributes c,
454                       master.dbo.sysattributes a, sysindexes s
455                   where t.object_type = "T"
456                       and t.object = s.id
457                       and c.class = 0 and c.attribute = 0
458                       and a.class = 0 and a.attribute = 1
459                       and t.class = c.object
460                       and t.class = a.object
461                       and t.attribute = a.object_info1
462   
463                   delete from #vhash_tbls where attrib <> 'hash key factors'
464   
465                   update #vhash_tbls
466                   set emptypg_cnt = emptypgcnt
467                   from systabstats, #vhash_tbls
468                   where systabstats.id = #vhash_tbls.id
469   
470                   select @empty_vhash_pgs = sum(emptypg_cnt)
471                   from #vhash_tbls
472   
473                   if (@empty_vhash_pgs is NULL)
474                   begin
475                       select @empty_vhash_pgs = 0
476                   end
477               end
478   
479               --
480               -- Compute the summary results by adding page counts from individual
481               -- data objects. Add to that the count of pages for syslogs.
482               --
483               set @reserved_space_in_db = (@reserved_pgs + @sl_used_pages_wo_APs)
484   
485                   -- This is the expected output from this sproc; i.e. 'data' field
486                   -- includes data-only pages
487                   --	     +  pages actually used by syslogs not including alloc pgs
488                   --	  less  pages currently empty in VHash tables.
489                   --
490                   , @data_space_in_db = (@dataonly_pgs + @sl_used_pages_wo_APs
491                   - @empty_vhash_pgs)
492   
493                   , @index_space_in_db = @index_pgs
494   
495                   , @unused_space_in_db = (@reserved_pgs - @used_pgs + @empty_vhash_pgs)
496   
497               -- Convert the total pages to space used in Kilo bytes.
498               select reserved = convert(varchar(30),
499                   (@reserved_space_in_db * @pgsize_KB))
500                   + " KB"
501   
502                   , data = convert(varchar(30),
503                   (@data_space_in_db * @pgsize_KB))
504                   + " KB"
505   
506                   , index_size = convert(varchar(30),
507                   (@index_space_in_db * @pgsize_KB))
508                   + " KB"
509   
510                   , unused = convert(varchar(30),
511                   (@unused_space_in_db * @pgsize_KB))
512                   + " KB"
513               into #fmtpgcnts
514   
515               exec sp_autoformat #fmtpgcnts
516   
517               if (@dbg_timing != 0)
518               begin
519                   set @time_end = current_bigtime()
520   
521                   print " "
522                   exec sp_prt_bigtimediff "Time taken to generate space usage for syslogs"
523                       , @time_before_syslogs
524                       , @time_after_syslogs
525   
526                   exec sp_prt_bigtimediff "Time taken for caching spaceusage for all objects"
527                       , @time_after_syslogs
528                       , @time_after_caching_spusage
529   
530                   exec sp_prt_bigtimediff "Time taken for parsing spaceusage for all objects"
531                       , @time_after_caching_spusage
532                       , @time_after_parsing_spusage
533   
534                   exec sp_prt_bigtimediff "Total time taken for procedure"
535                       , @time_start, @time_end
536               end
537   
538               -- Drop #table after timing so as not to -vely affect the
539               -- timing metrics (due to normal sproc #table book-keeping).
540               --
541               drop table #fmtpgcnts
542   
543           end -- }
544   
545           /*
546           **  We want a particular object.
547           */
548           else -- @objname IS NOT NULL. (@objid has been established previously.)
549           begin -- {
550   
551               declare @time_after_caching_spusage_allindexes bigtime
552                   , @time_before_pgcounts_thisobj bigtime
553                   , @time_after_pgcounts_thisobj bigtime
554   
555               set @time_after_caching_spusage_allindexes = NULL
556   
557               if (@tabname = "syslogs")
558               begin
559                   declare @total_pages bigint, /* total allocatable logspace */
560                       @free_pages bigint, /* log free space in pages */
561                       @used_pages bigint, /* allocated log space */
562                       @used_pages_wo_APs
563                       bigint, /* allocated log space, without
564                       ** including the allocation
565                       ** pages.
566                       */
567                       @clr_pages bigint /* log space reserved for CLRs */
568   
569                   -- Get the space usage for the table in question.
570                   if (@dbg_timing != 0)
571                       set @time_before_pgcounts_thisobj = current_bigtime()
572   
573                   exec sp_spaceused_syslogs @total_pages output
574                       , @free_pages output
575                       , @used_pages output
576                       , @used_pages_wo_APs output
577                       , @clr_pages output
578   
579                   if (@dbg_timing != 0)
580                       set @time_after_pgcounts_thisobj = current_bigtime()
581   
582                   select name = convert(char(15), @tabname),
583                       total_pages = convert(char(15), @total_pages),
584                       free_pages = convert(char(15), @free_pages),
585                       used_pages = convert(char(15), @used_pages),
586                       reserved_pages = convert(char(15), @clr_pages)
587   
588                   if (@dbg_timing != 0)
589                   begin
590                       exec sp_prt_bigtimediff "Time taken to generate space usage for syslogs"
591                           , @time_before_pgcounts_thisobj
592                           , @time_after_pgcounts_thisobj
593                   end
594               end
595               else
596               begin -- { non-Syslogs object
597   
598                   -- If listing for indexes has been requested, and there are
599                   -- some indexes on the object, then capture the space usage
600                   -- metrics for each index (including LOB pages; indid=255)
601                   -- as spaceusage() outputs. That will be parsed a bit later on
602                   -- to identify the individual fields of interest.
603                   --
604                   if ((@list_indices = 1)
605                           and (select count(*) from dbo.sysindexes i
606                               where i.id = @objid
607                                   and i.indid >= 1) > 0)
608                   begin -- {
609                       select index_name = i.name
610                           , i.indid
611                           , spaceusage = spaceusage(@dbid, i.id, i.indid)
612   
613                           -- Value in # of pages for all indexes
614                           , size = convert(bigint, 0)
615                           , reserved = convert(bigint, 0)
616                           , used = convert(bigint, 0)
617   
618                           -- Converted to # of KB, as a string
619                           , size_KB = convert(varchar(30), " ")
620                           , reserved_KB = convert(varchar(30), " ")
621                           , unused_KB = convert(varchar(30), " ")
622   
623                       into #formatpgcounts
624                       from sysindexes i
625                       where i.id = @objid
626                           and i.indid >= 1
627   
628                       if (@dbg_timing != 0)
629                       begin
630                           set @time_after_caching_spusage_allindexes
631                               = current_bigtime()
632                       end
633   
634                       -- Extract out the individual fields of interest
635                       -- from the built-in's result into individual cols
636                       --
637                       update #formatpgcounts
638                       set size = dbo.sp_f_getuint(spaceusage,
639                               "index pages",
640                               default, default)
641                           , reserved = dbo.sp_f_getuint(spaceusage,
642                               "reserved pages",
643                               default, default)
644                           , used = dbo.sp_f_getuint(spaceusage,
645                               "used pages",
646                               default, default)
647   
648                       if (@dbg_show_interim_results != 0)
649                       begin
650                           print " "
651                           print "Intermediate rows cached in #formatpgcounts"
652                           print " "
653                           exec sp_autoformat @fulltabname = "#formatpgcounts"
654                               , @selectlist = "index_name, indid, spaceusage, size, reserved, used"
655                               , @orderby = "order by indid"
656                           print " "
657   
658                       end
659   
660                       -- Convert the # of pages to # of KB
661                       --
662                       update #formatpgcounts
663                       set size_KB = convert(varchar(30),
664                           (size * @pgsize_KB))
665                           + " KB"
666                           , reserved_KB = convert(varchar(30),
667                           (reserved * @pgsize_KB))
668                           + " KB"
669                           , unused_KB = convert(varchar(30),
670                           (reserved - size) * @pgsize_KB)
671                           + " KB"
672   
673                       exec sp_autoformat @fulltabname = #formatpgcounts
674                           , @selectlist = "index_name, size = size_KB, reserved=reserved_KB, unused=unused_KB"
675                           , @orderby = "order by indid"
676   
677                       drop table #formatpgcounts
678                   end -- }
679   
680                   -- Get the space usage for the table in question.
681                   if (@dbg_timing != 0)
682                       set @time_before_pgcounts_thisobj = current_bigtime()
683   
684                   -- Then parse the output to extract the individual fields of
685                   -- interest.
686                   --
687                   set @spaceusage = spaceusage(@dbid, @objid)
688   
689                   if (@dbg_timing != 0)
690                       set @time_after_pgcounts_thisobj = current_bigtime()
691   
692                   set @reserved_pgs = dbo.sp_f_getuint(@spaceusage,
693                           "reserved pages",
694                           default, default)
695                       , @data_pgs = dbo.sp_f_getuint(@spaceusage,
696                           "data pages",
697                           default, default)
698                       , @index_pgs = dbo.sp_f_getuint(@spaceusage,
699                           "index pages",
700                           default, default)
701                       , @used_pgs = dbo.sp_f_getuint(@spaceusage,
702                           "used pages",
703                           default, default)
704                       , @rowcount = dbo.sp_f_getbigint(@spaceusage,
705                           "row count",
706                           default, default)
707   
708                   /*
709                   ** Check whether the table is Virtually hashed. For Virtually
710                   ** Hashed tables, we maintain the number of empty pages in
711                   ** systabstats. Compute the #data pages and #unused pages
712                   ** based on that value.
713                   */
714                   set @empty_vhash_pgs = NULL
715                   if exists (select convert(char(30), a.char_value)
716                           from sysattributes t
717                           , master.dbo.sysattributes c
718                           , master.dbo.sysattributes a
719                           where t.object_type = "T"
720                               and t.object = @objid
721                               and c.class = 0 and c.attribute = 0
722                               and a.class = 0 and a.attribute = 1
723                               and t.class = c.object
724                               and t.class = a.object
725                               and t.attribute = a.object_info1
726                               and a.char_value = 'hash key factors')
727                   begin
728                       select @empty_vhash_pgs = ts.emptypgcnt
729                       from systabstats ts
730                       where ts.id = @objid
731                   end
732   
733                   -- Handle error in SQL, or Vhash table does not exist.
734                   if (@empty_vhash_pgs IS NULL)
735                   begin
736                       select @empty_vhash_pgs = 0
737                   end
738   
739                   if (@dbg_show_interim_results != 0)
740                   begin
741                       print " "
742                       print "%1!", @spaceusage
743                       print "Vhash empty pages=%1!", @empty_vhash_pgs
744                   end
745   
746                   -- Adjust for data and unused pages, if the table happens to be
747                   -- a VHash table.
748                   --
749                   set @dataonly_pgs = (@data_pgs - @index_pgs - @empty_vhash_pgs)
750                       , @unused_pgs = (@reserved_pgs - @used_pgs
751                       + @empty_vhash_pgs)
752   
753                   -- Generate the output metrics for the requested table.
754                   select name = @tabname
755                       , rowtotal = convert(varchar(30), @rowcount)
756   
757                       , reserved = convert(varchar(30),
758                       (@reserved_pgs * @pgsize_KB))
759                       + " KB"
760   
761                       , data = convert(varchar(30),
762                       (@dataonly_pgs * @pgsize_KB))
763                       + " KB"
764   
765                       , index_size = convert(varchar(30),
766                       (@index_pgs * @pgsize_KB))
767                       + " KB"
768   
769                       , unused = convert(varchar(30),
770                       (@unused_pgs * @pgsize_KB))
771                       + " KB"
772                   into #fmtpgcounts
773   
774                   exec sp_autoformat #fmtpgcounts
775   
776                   -- Generate the timing metrics, if requested
777                   if (@dbg_timing != 0)
778                   begin
779                       set @time_end = current_bigtime()
780   
781                       print " "
782   
783                       -- Only need to report timing for processing indexes
784                       -- if user had requested to show index space usage
785                       -- in a separate chunk, _AND_ we had some indexes to
786                       -- process.
787                       --
788                       if ((@list_indices = 1)
789                               and (@dbg_timing != 0)
790                               and (@time_after_caching_spusage_allindexes
791                                   IS NOT NULL))
792                       begin
793   
794                           exec sp_prt_bigtimediff "Time taken to cache spaceusage for only indexes (in #table)"
795                               , @time_start
796                               , @time_after_caching_spusage_allindexes
797                       end
798   
799                       exec sp_prt_bigtimediff "Time taken to cache spaceusage for entire object"
800                           , @time_before_pgcounts_thisobj
801                           , @time_after_pgcounts_thisobj
802   
803                       exec sp_prt_bigtimediff "Total time taken for procedure"
804                           , @time_start, @time_end
805                   end
806   
807                   -- Drop #table after timing so as not to -vely affect the
808                   -- timing metrics (due to normal sproc #table book-keeping).
809                   --
810                   drop table #fmtpgcounts
811   
812                   /* Print warning information for Volatile table. */
813                   select @tab_volatile = 0x8
814                   if ((@sysstat4 & @tab_volatile) != 0)
815                   begin
816                       print "NOTE: The table is VOLATILE table so the reported data may be inaccurate."
817                   end
818               end -- } non-Syslogs object
819   
820               -- Produce warning message for tables w/DROP'ped columns
821               if exists (select 1 from sysattributes
822                       where object_type = "TI"
823                           and (object_info1 & 4096) = 4096
824                           and object = @objid)
825               begin
826                   select @msg = "Table "
827                       + @objname
828                       + " has columns dropped by no datacopy method."
829                   print @msg
830   
831                   select @msg = "Space for such columns won't be freed until "
832                       + "next reorg-rebuild or datacopy operation."
833                   print @msg
834               end
835   
836           end -- } @objname IS NOT NULL
837           return (0)
838       end -- }
839   


exec sp_procxmode 'sp_spaceused', 'AnyMode'
go

Grant Execute on sp_spaceused to public
go
RESULT SETS
sp_spaceused_rset_001

DEFECTS
 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 244
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 286
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 515
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 673
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 774
 QCSC 4 Costly 'select count()', use 'exists()' 445
 QCSC 4 Costly 'select count()', use 'exists()' 605
 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]
456
 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]
459
 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]
460
 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]
723
 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]
724
 QPUI 4 Join or Sarg with Un-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, object_info1, object_type}
822
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 174
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 261
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 263
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 264
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 457
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 458
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 459
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 460
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 461
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 607
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 626
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 721
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 722
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 723
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 724
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 725
 TNOI 4 Table with no index master..sysoptions master..sysoptions
 TNOU 4 Table with no unique index master..spt_values master..spt_values
 VRUN 4 Variable is read and not initialized @dbsize 274
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 158
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 173
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 450
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 456
 MAW1 3 Warning message on %name% sybsystemprocs..systabstats.id: Warning message on systabstats 468
 MAW1 3 Warning message on %name% tempdb..#vhash_tbls.id: Warning message on #vhash_tbls_crby_sybsystemprocs__sp_spaceused 468
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 606
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 611
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 625
 MAW1 3 Warning message on %name% sybsystemprocs..systabstats.id: Warning message on systabstats 730
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 92
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 95
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 98
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 447
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 813
 MDYN 3 Proc uses Dynamic SQL but is not flagged with Dynamic Ownership Chain 20
 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..sysoptions  
 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 125
 MNER 3 No Error Check should check @@error after select into 236
 MNER 3 No Error Check should check return value of exec 244
 MNER 3 No Error Check should check @@error after select into 272
 MNER 3 No Error Check should check return value of exec 286
 MNER 3 No Error Check should check return value of exec 345
 MNER 3 No Error Check should check @@error after select into 449
 MNER 3 No Error Check should check @@error after delete 463
 MNER 3 No Error Check should check @@error after update 465
 MNER 3 No Error Check should check @@error after select into 498
 MNER 3 No Error Check should check return value of exec 515
 MNER 3 No Error Check should check return value of exec 522
 MNER 3 No Error Check should check return value of exec 526
 MNER 3 No Error Check should check return value of exec 530
 MNER 3 No Error Check should check return value of exec 534
 MNER 3 No Error Check should check return value of exec 573
 MNER 3 No Error Check should check return value of exec 590
 MNER 3 No Error Check should check @@error after select into 609
 MNER 3 No Error Check should check @@error after update 637
 MNER 3 No Error Check should check return value of exec 653
 MNER 3 No Error Check should check @@error after update 662
 MNER 3 No Error Check should check return value of exec 673
 MNER 3 No Error Check should check @@error after select into 754
 MNER 3 No Error Check should check return value of exec 774
 MNER 3 No Error Check should check return value of exec 794
 MNER 3 No Error Check should check return value of exec 799
 MNER 3 No Error Check should check return value of exec 803
 MUCO 3 Useless Code Useless Brackets in create proc 20
 MUCO 3 Useless Code Useless Begin-End Pair 25
 MUCO 3 Useless Code Useless Brackets 77
 MUCO 3 Useless Code Useless Brackets 78
 MUCO 3 Useless Code Useless Brackets 90
 MUCO 3 Useless Code Useless Brackets 92
 MUCO 3 Useless Code Useless Brackets 95
 MUCO 3 Useless Code Useless Brackets 98
 MUCO 3 Useless Code Useless Brackets 133
 MUCO 3 Useless Code Useless Brackets 136
 MUCO 3 Useless Code Useless Brackets 146
 MUCO 3 Useless Code Useless Brackets 152
 MUCO 3 Useless Code Useless Brackets 183
 MUCO 3 Useless Code Useless Brackets 192
 MUCO 3 Useless Code Useless Brackets 197
 MUCO 3 Useless Code Useless Brackets 231
 MUCO 3 Useless Code Useless Brackets 290
 MUCO 3 Useless Code Useless Brackets 294
 MUCO 3 Useless Code Useless Brackets 306
 MUCO 3 Useless Code Useless Brackets 312
 MUCO 3 Useless Code Useless Brackets 317
 MUCO 3 Useless Code Useless Brackets 325
 MUCO 3 Useless Code Useless Brackets 351
 MUCO 3 Useless Code Useless Brackets 376
 MUCO 3 Useless Code Useless Brackets 381
 MUCO 3 Useless Code Useless Brackets 396
 MUCO 3 Useless Code Useless Brackets 400
 MUCO 3 Useless Code Useless Brackets 428
 MUCO 3 Useless Code Useless Brackets 445
 MUCO 3 Useless Code Useless Brackets 473
 MUCO 3 Useless Code Useless Brackets 499
 MUCO 3 Useless Code Useless Brackets 503
 MUCO 3 Useless Code Useless Brackets 507
 MUCO 3 Useless Code Useless Brackets 511
 MUCO 3 Useless Code Useless Brackets 517
 MUCO 3 Useless Code Useless Brackets 557
 MUCO 3 Useless Code Useless Brackets 570
 MUCO 3 Useless Code Useless Brackets 579
 MUCO 3 Useless Code Useless Brackets 588
 MUCO 3 Useless Code Useless Brackets 604
 MUCO 3 Useless Code Useless Brackets 628
 MUCO 3 Useless Code Useless Brackets 648
 MUCO 3 Useless Code Useless Brackets 664
 MUCO 3 Useless Code Useless Brackets 667
 MUCO 3 Useless Code Useless Brackets 681
 MUCO 3 Useless Code Useless Brackets 689
 MUCO 3 Useless Code Useless Brackets 734
 MUCO 3 Useless Code Useless Brackets 739
 MUCO 3 Useless Code Useless Brackets 758
 MUCO 3 Useless Code Useless Brackets 762
 MUCO 3 Useless Code Useless Brackets 766
 MUCO 3 Useless Code Useless Brackets 770
 MUCO 3 Useless Code Useless Brackets 777
 MUCO 3 Useless Code Useless Brackets 788
 MUCO 3 Useless Code Useless Brackets 814
 MUCO 3 Useless Code Useless Brackets 837
 QAFM 3 Var Assignment from potentially many rows 77
 QAFM 3 Var Assignment from potentially many rows 259
 QAFM 3 Var Assignment from potentially many rows 728
 QCRS 3 Conditional Result Set 582
 QCTC 3 Conditional Table Creation 236
 QCTC 3 Conditional Table Creation 272
 QCTC 3 Conditional Table Creation 299
 QCTC 3 Conditional Table Creation 449
 QCTC 3 Conditional Table Creation 498
 QCTC 3 Conditional Table Creation 609
 QCTC 3 Conditional Table Creation 754
 QDIS 3 Check correct use of 'select distinct' 449
 QGWO 3 Group by/Distinct/Union without order by 449
 QISO 3 Set isolation level 72
 QNAJ 3 Not using ANSI Inner Join 453
 QNAJ 3 Not using ANSI Inner Join 467
 QNAJ 3 Not using ANSI Inner Join 716
 QNUA 3 Should use Alias: Column emptypgcnt should use alias systabstats 466
 QNUA 3 Should use Alias: Table sybsystemprocs..systabstats 467
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
241
 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}
261
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
284
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: sysindexes.csysindexes unique clustered
(id, indid)
Intersection: {id}
Uncovered: [indid]
456
 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]
459
 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]
460
 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]
723
 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]
724
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: systabstats.csystabstats unique clustered
(id, indid, partitionid)
Intersection: {id}
730
 VNRD 3 Variable is not read @used_pages_wo_APs 576
 VUNU 3 Variable is not used @length 34
 VUNU 3 Variable is not used @time_after_pgcounts_allobjs 226
 MDYS 2 Dynamic SQL Marker 270
 MRST 2 Result Set Marker 582
 MSUB 2 Subquery Marker 103
 MSUB 2 Subquery Marker 171
 MSUB 2 Subquery Marker 306
 MSUB 2 Subquery Marker 317
 MSUB 2 Subquery Marker 445
 MSUB 2 Subquery Marker 605
 MSUB 2 Subquery Marker 715
 MSUB 2 Subquery Marker 821
 MTR1 2 Metrics: Comments Ratio Comments: 34% 20
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 82 = 85dec - 5exi + 2 20
 MTR3 2 Metrics: Query Complexity Complexity: 305 20
 PRED_QUERY_COLLECTION 2 {a=sybsystemprocs..sysattributes, a2=master..sysattributes, a3=master..sysattributes, i=sybsystemprocs..sysindexes} 0 449
 PRED_QUERY_COLLECTION 2 {a=master..sysattributes, a2=sybsystemprocs..sysattributes, a3=master..sysattributes} 0 715

DATA PROPAGATION detailed
ColumnWritten To
@objnamesp_spaceused_rset_001.name

DEPENDENCIES
PROCS AND TABLES USED
read_writes table tempdb..#vhash_tbls (1) 
reads table master..sysdatabases (1)  
reads table master..spt_values (1)  
calls proc sybsystemprocs..sp_namecrack  
reads table master..sysoptions (1)  
calls proc sybsystemprocs..sp_prt_bigtimediff  
   calls proc sybsystemprocs..sp_get_bigtimediff  
writes table tempdb..#spaceused1result (1) 
reads table master..sysusages (1)  
reads table sybsystemprocs..systabstats  
reads table sybsystemprocs..sysobjects  
reads table sybsystemprocs..sysattributes  
writes table tempdb..#formatpgcounts (1) 
writes table tempdb..#fmtpgcnts (1) 
calls proc sybsystemprocs..sp_autoformat  
   writes table sybsystemprocs..sp_autoformat_rset_005 
   writes table sybsystemprocs..sp_autoformat_rset_002 
   calls proc sybsystemprocs..sp_namecrack  
   reads table master..syscolumns (1)  
   writes table sybsystemprocs..sp_autoformat_rset_004 
   read_writes table tempdb..#colinfo_af (1) 
   reads table tempdb..systypes (1)  
   reads table master..systypes (1)  
   writes table sybsystemprocs..sp_autoformat_rset_001 
   calls proc sybsystemprocs..sp_autoformat  
   reads table tempdb..syscolumns (1)  
   writes table sybsystemprocs..sp_autoformat_rset_003 
writes table tempdb..#spaceused2result (1) 
writes table sybsystemprocs..sp_spaceused_rset_001 
calls proc sybsystemprocs..sp_spaceused_syslogs  
   reads table master..sysusages (1)  
   reads table master..sysdatabases (1)  
writes table tempdb..#fmtpgcounts (1) 
reads table sybsystemprocs..sysindexes  
read_writes table tempdb..#sysoptions (1) 
reads table master..sysattributes (1)