DatabaseProcApplicationCreatedLinks
sybsystemprocssp_helpdb  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     /*	4.8	1.1	06/14/90	sproc/src/help */
4     /*
5     ** Messages for "sp_helpdb"             17590
6     **
7     ** 17111, "log only"
8     ** 17590, "The specified database does not exist."
9     ** 17591, "no options set"
10    ** 17592, " -- unused by any segments --"
11    ** 17714, "not applicable"
12    ** 17600, "sp_helpdb: order value '%1!' is not valid. Valid values are 
13    **	'lstart' and 'device_name'. Using default value 'lstart'."
14    ** 17609, "Device allocation is not displayed for local temporary 
15    **	   database '%1!'. To display this information, execute the 
16    **	   procedure on the owner instance '%2!'."
17    ** 17909, "log only unavailable". 
18    */
19    create or replace procedure sp_helpdb
20        @dbname varchar(255) = NULL, /* database name to change */
21        @order varchar(20) = 'lstart' /* Use 'device_name' to order
22    						** by device name
23    						*/
24    as
25    
26        declare @showdev int,
27            @showinstance int,
28            @allopts int,
29            @all2opts int,
30            @all3opts int,
31            @all4opts int,
32            @all5opts int,
33            @dbstatus4 int,
34            @thisopt int,
35            @optmask int,
36            @optmax int,
37            @template_mask int,
38            @imdb_mask int,
39            @pgcomp_mask int,
40            @rowcomp_mask int,
41            @has_template_mask int,
42            @local_tempdb_mask int,
43            @pagekb unsigned int,
44            @msg varchar(1024),
45            @sptlang int,
46            @na_phrase varchar(30), /* length of German */
47            @sqlbuf varchar(1024),
48            @len1 int, @len2 int, @len3 int,
49            @q char(1), /* quote sign */
50            @instancename varchar(255),
51            @flmode_class int,
52            @flmode int,
53            @flmode_all int,
54            @flmode_desc varchar(100),
55            @holesize numeric(20, 0),
56            @location int,
57            @select_list varchar(255),
58            @encrypted int,
59            @encryption_in_progress int,
60            @decryption_in_progress int,
61            @encrypted_partly int,
62            @status5 int,
63            @str1 varchar(30),
64            @str2 varchar(30),
65            @is_sidb int
66        if @@trancount = 0
67        begin
68            set chained off
69        end
70    
71        set transaction isolation level 1
72    
73        select @sptlang = @@langid
74    
75        if @@langid != 0
76        begin
77            if not exists (
78                    select * from master.dbo.sysmessages where error
79                        between 17050 and 17069
80                        and langid = @@langid)
81                select @sptlang = 0
82            else
83            if not exists (
84                    select * from master.dbo.sysmessages where error
85                        between 17110 and 17119
86                        and langid = @@langid)
87                select @sptlang = 0
88        end
89    
90        set nocount on
91    
92        /*
93        **  If no database name given, get 'em all.  Otherwise, count how many
94        **  databases match the specified name.
95        */
96        if @dbname is null
97            select @dbname = "%",
98                @showdev = count(*) from master.dbo.sysdatabases
99        else
100           select @showdev = count(*)
101           from master.dbo.sysdatabases
102           where name like @dbname
103   
104       /*
105       **  Sure the database exists
106       */
107       if @showdev = 0
108       begin
109           /* 17590, "The specified database does not exist." */
110           raiserror 17590
111           return (1)
112       end
113   
114       /*
115       **  Set allopts to be the sum of all possible user-settable database status
116       **  bits.  (Note that there are 2 groups of such bits.)  If we can't get
117       **  the option mask from spt_values, guess at the correct value.
118       */
119       select @allopts = number
120       from master.dbo.spt_values
121       where type = "D"
122           and name = "ALL SETTABLE OPTIONS"
123       if (@allopts is NULL)
124           select @allopts = 4 | 8 | 16 | 512 | 1024 | 2048 | 4096 | 8192
125   
126       select @all2opts = number
127       from master.dbo.spt_values
128       where type = "D2"
129           and name = "ALL SETTABLE OPTIONS"
130       if (@all2opts is NULL)
131           select @all2opts = 1 | 2 | 4 | 8 | 64
132   
133       select @all3opts = number
134       from master.dbo.spt_values
135       where type = "D3"
136           and name = "ALL SETTABLE OPTIONS"
137       if (@all3opts is NULL)
138           select @all3opts = 0
139   
140       select @all4opts = number
141       from master.dbo.spt_values
142       where type = "D4"
143           and name = "ALL SETTABLE OPTIONS"
144       if (@all4opts is NULL)
145           select @all4opts = 0
146   
147       select @all5opts = number
148       from master.dbo.spt_values
149       where type = "D5"
150           and name = "ALL SETTABLE OPTIONS"
151       if (@all5opts is NULL)
152           select @all5opts = 0
153   
154       /*
155       ** @allopts (sysdatabases.status options) should also contain some
156       ** NON-settable options that we want to check for:
157       **	 32 = "don't recover"
158       **	256 = "not recovered"
159       **    16384 = "erase residual data"
160       */
161       select @allopts = @allopts | 32 | 256 | 16384
162   
163       /*
164       ** @all2opts (sysdatabases.status2 options) should also contain a
165       ** NON-settable option that we want to check for:
166       **	 16 = "offline"
167       **	128 = "has suspect objects"
168       **     1024 = "online for standby access"
169       **    32768 = "mixed log and data" 
170       */
171       select @all2opts = @all2opts | 16 | 128 | 1024 | 32768
172   
173       /*
174       ** @all3opts (sysdatabases.status3 options) should also contain 
175       ** NON-settable options that we want to check for:
176       **
177       **	0128 = "quiesce database"
178       **	
179       **	if SMP
180       **		256 = "user created temp db"
181       **	if SDC
182       **	        256 = "local user temp db"
183       **		536870912 = "local system temp db"
184       **		1073741824 = "global user temp db"
185       **
186       **	1024 = "async log service"
187       **	2048 = "delayed commit"
188       **   4194304 = "archive database"
189       **   8388608 = "compressed data"
190       ** 134217728 = "compressed log"
191       */
192       select @all3opts = @all3opts | 128 | 256 | 1024 | 2048 | 4194304 | 8388608 | 134217728
193       if @@clustermode = "shared disk cluster"
194       begin
195           select @all3opts =
196               @all3opts | 536870912 | 1073741824
197           select @local_tempdb_mask = number
198           from master.dbo.spt_values
199           where type = "D3" and name = "LOCAL TEMPDB STATUS MASK"
200       end
201       else
202       begin
203           select @local_tempdb_mask = 0
204       end
205   
206       /*
207       ** @all4opts (sysdatabases.status4 options) should also contain 
208       ** NON-settable options that we want to check for:
209       **
210       **	4096 = "in-memory database"
211       **	512  = "has template"
212       **	1024 = "is template database"
213       **      8192 = "user-created" "enhanced performance temp db"
214       */
215       select @all4opts = @all4opts | 256 | 1024 | 4096 | 16777216 | 33554432,
216           @has_template_mask = 512,
217           @template_mask = 1024,
218           @imdb_mask = 4096,
219           @pgcomp_mask = 16777216,
220           @rowcomp_mask = 33554432
221   
222       /*
223       ** @all5opts (sysdatabases.status5 options) should also contain 
224       ** NON-settable options that we want to check for:
225       **
226       **	1 = "encrypted"
227       **	2 = "encryption in progress"
228       **	4 = "decryption in progress"
229       **	8 = "encrypted partly"
230       **	16 = "decrypted partly"
231       */
232       select @all5opts = @all5opts | 1 | 2 | 4 | 8 | 16,
233           @encrypted = 1,
234           @encryption_in_progress = 2,
235           @decryption_in_progress = 4,
236           @encrypted_partly = 8
237   
238       /*
239       **  Since we examine the status bits in sysdatabase and turn them
240       **  into english, we need a temporary table to build the descriptions.
241       */
242       create table #spdbdesc
243       (
244           dbid smallint null,
245           dbdesc varchar(777) null
246       )
247   
248       /*
249       **  Initialize #spdbdesc from sysdatabases
250       */
251       insert into #spdbdesc(dbid)
252       select dbid
253       from master.dbo.sysdatabases
254       where name like @dbname
255       /*
256       **  Now for each dbid in #spdbdesc, build the database status
257       **  description.
258       */
259       declare @curdbid smallint /* the one we're currently working on */
260       declare @dbdesc varchar(777) /* the total description for the db */
261       declare @bitdesc varchar(50) /* the bit description for the db */
262   
263       /* For regular databases, we don't need to show the owner instances. */
264       select @showinstance = 0
265   
266       /*
267       ** Get full logging option mask for all
268       */
269       select @flmode_class = 38
270       select @flmode_all = object_info1
271       from master..sysattributes
272       where class = @flmode_class and object = 1 and attribute = 0
273   
274       select @optmax = max(object_info1)
275       from master..sysattributes
276       where class = @flmode_class and object = 1 and attribute != 0
277   
278       /*
279       **  Set @curdbid to the first dbid.
280       */
281       select @curdbid = min(dbid)
282       from #spdbdesc
283   
284       while @curdbid is not NULL
285       begin
286           /*
287           **  Initialize @dbdesc.
288           */
289           select @dbdesc = ""
290   
291           /*
292           **  Check status options (spt_values.type = "D")
293           */
294           select @thisopt = 1
295           select @optmask = @allopts /* status options */
296           while (@optmask != 0) /* until all set options noted ... */
297           begin
298               /*
299               ** If this option is user-settable, check for it
300               */
301               if (@optmask & @thisopt = @thisopt)
302               begin
303                   select @bitdesc = null
304   
305                   select @bitdesc = m.description
306                   from master.dbo.spt_values v,
307                       master.dbo.sysdatabases d,
308                       master.dbo.sysmessages m
309                   where d.dbid = @curdbid
310                       and v.type = "D"
311                       and d.status & v.number = @thisopt
312                       and v.number = @thisopt
313                       and v.msgnum = m.error
314                       and isnull(m.langid, 0) = @sptlang
315                   if @bitdesc is not null
316                   begin
317                       if @dbdesc != ""
318                           select @dbdesc = @dbdesc + ", " + @bitdesc
319                       else select @dbdesc = @bitdesc
320                   end
321   
322                   /* Turn off this status bit in the options mask */
323                   select @optmask = @optmask & ~ (@thisopt)
324               end
325   
326               /*
327               ** Get the next option bit.  Check for integer overflow for
328               ** bit 31 (0x80000000).
329               */
330               if (@thisopt < 1073741824)
331                   select @thisopt = @thisopt * 2
332               else
333                   select @thisopt = - 2147483648
334           end
335   
336           /*
337           **  Check status2 options (spt_values.type = "D2")
338           */
339           select @thisopt = 1
340           select @optmask = @all2opts /* status2 options */
341           while (@optmask != 0) /* until all set options noted ... */
342           begin
343               /*
344               ** If this option is user-settable, check for it
345               */
346               if (@optmask & @thisopt = @thisopt)
347               begin
348                   select @bitdesc = null
349   
350                   select @bitdesc = m.description
351                   from master.dbo.spt_values v,
352                       master.dbo.sysdatabases d,
353                       master.dbo.sysmessages m
354                   where d.dbid = @curdbid
355                       and v.type = "D2"
356                       and d.status2 & v.number = @thisopt
357                       and v.number = @thisopt
358                       and v.msgnum = m.error
359                       and isnull(m.langid, 0) = @sptlang
360                   if @bitdesc is not null
361                   begin
362                       if @dbdesc != ""
363                           select @dbdesc = @dbdesc + ", " + @bitdesc
364                       else select @dbdesc = @bitdesc
365                   end
366   
367                   /* Turn off this status bit in the options mask */
368                   select @optmask = @optmask & ~ (@thisopt)
369               end
370   
371               /*
372               ** Get the next option bit.  Check for integer overflow for
373               ** bit 31 (0x80000000).
374               */
375               if (@thisopt < 1073741824)
376                   select @thisopt = @thisopt * 2
377               else
378                   select @thisopt = - 2147483648
379           end
380   
381           /*
382           **  Check status3 options (spt_values.type = "D3")
383           */
384           select @thisopt = 1
385           select @optmask = @all3opts /* status3 options */
386           while (@optmask != 0) /* until all set options noted ... */
387           begin -- {
388               /*
389               ** If this option is user-settable, check for it
390               */
391               if (@optmask & @thisopt = @thisopt)
392               begin
393                   select @bitdesc = null
394   
395                   select @bitdesc = m.description
396                   from master.dbo.spt_values v,
397                       master.dbo.sysdatabases d,
398                       master.dbo.sysmessages m
399                   where d.dbid = @curdbid
400                       and v.type = "D3"
401                       and d.status3 & v.number = @thisopt
402                       and v.number = @thisopt
403                       and v.msgnum = m.error
404                       and isnull(m.langid, 0) = @sptlang
405   
406                   if ((@bitdesc is not null)
407                           and (@thisopt = 256))
408                   begin -- {
409                       /*
410                       ** Check if it's an Implicit or
411                       ** Explicit tempdb.
412                       */
413                       select @dbstatus4 = status4
414                       from master.dbo.sysdatabases
415                       where dbid = @curdbid
416   
417                       /*
418                       ** Check if its explicit tempdb with
419                       ** "no_recovery" by comparing it with
420                       ** the decimal value of
421                       ** DBT4_EXPLICIT_NO_REC_TEMPDB.
422                       ** If explicit, output "user-created
423                       ** enhanced performance temp db".
424                       **
425                       ** If implicit (normal or IMDB) tempdb
426                       ** then output "user created temp db".
427                       */
428                       if (@dbstatus4 & 8192 = 8192)
429                       begin
430                           select @str1 = m.description
431                           from master.dbo.spt_values v
432                           , master.dbo.sysmessages m
433                           where v.type = "D4"
434                               and v.number = 8192
435                               and v.msgnum = 17164
436                               and m.error = v.msgnum
437   
438                           select @str2 = m.description
439                           from master.dbo.spt_values v
440                           , master.dbo.sysmessages m
441                           where v.type = "D4"
442                               and v.number = 8192
443                               and v.msgnum = 17165
444                               and m.error = v.msgnum
445   
446                           /*
447                           ** In case of a rare error that we
448                           ** could not retrieve one or both of
449                           ** the messages, return the final
450                           ** message as NULL so as to signal some
451                           ** unforeseen error.
452                           */
453                           if ((@str1 IS NOT NULL)
454                                   and (@str2 IS NOT NULL))
455   
456                               select @bitdesc = @str1
457                                   + " "
458                                   + @str2
459   
460                           else
461                               select @bitdesc = NULL
462                       end
463                   end -- }
464   
465                   if @bitdesc is not null
466                   begin
467                       if @dbdesc != ""
468                           select @dbdesc = @dbdesc + ", " + @bitdesc
469                       else
470                           select @dbdesc = @bitdesc
471   
472                       if (@thisopt & @local_tempdb_mask = @thisopt)
473                           select @showinstance = 1
474                   end
475   
476                   /* Turn off this status bit in the options mask */
477                   select @optmask = @optmask & ~ (@thisopt)
478               end -- }
479   
480               /*
481               ** Get the next option bit.  Check for integer overflow for
482               ** bit 31 (0x80000000).
483               */
484               if (@thisopt < 1073741824)
485                   select @thisopt = @thisopt * 2
486               else
487                   select @thisopt = - 2147483648
488           end -- }
489   
490           /*
491           **  Check status4 options (spt_values.type = "D4")
492           */
493           select @thisopt = 1
494           select @optmask = @all4opts /* status4 options */
495           while (@optmask != 0) /* until all set options noted ... */
496           begin -- {
497               /*
498               ** If this option is user-settable, check for it
499               */
500               if (@optmask & @thisopt = @thisopt)
501               begin
502                   select @bitdesc = null
503   
504                   select @bitdesc = m.description
505                   from master.dbo.spt_values v,
506                       master.dbo.sysdatabases d,
507                       master.dbo.sysmessages m
508                   where d.dbid = @curdbid
509                       and v.type = "D4"
510                       and d.status4 & v.number = @thisopt
511                       and v.number = @thisopt
512                       and v.msgnum = m.error
513                       and isnull(m.langid, 0) = @sptlang
514                   if @bitdesc is not null
515                   begin
516                       if @dbdesc != ""
517                           select @dbdesc = @dbdesc + ", " + @bitdesc
518                       else select @dbdesc = @bitdesc
519                   end
520   
521                   /* Turn off this status bit in the options mask */
522                   select @optmask = @optmask & ~ (@thisopt)
523               end
524   
525               /*
526               ** Get the next option bit. Now bit 31 (0x80000000) is 
527               ** used for "deallocate first text page", handle it
528               ** here.
529               ** 
530               **	-2147483648 = "deallocate first text page"
531               */
532               if (@thisopt = - 2147483648)
533                   break
534               else if (@thisopt < 1073741824)
535                   select @thisopt = @thisopt * 2
536               else
537                   select @thisopt = - 2147483648
538           end -- }
539   
540           /*
541           **  Check status5 options (spt_values.type = "D5")
542           */
543           select @thisopt = 1
544           select @optmask = @all5opts /* status5 options */
545           while (@optmask != 0) /* until all set options noted ... */
546           begin
547               /*
548               ** If this option is user-settable, check for it
549               */
550               if (@optmask & @thisopt = @thisopt)
551               begin
552                   select @bitdesc = null
553   
554                   select @bitdesc = m.description, @status5 = d.status5
555                   from master.dbo.spt_values v,
556                       master.dbo.sysdatabases d,
557                       master.dbo.sysmessages m
558                   where d.dbid = @curdbid
559                       and v.type = "D5"
560                       and d.status5 & v.number = @thisopt
561                       and v.number = @thisopt
562                       and v.msgnum = m.error
563                       and isnull(m.langid, 0) = @sptlang
564                   if @bitdesc is not null
565                   begin
566                       /*
567                       ** Ignore first bit (i.e. encrypted) if we have
568                       ** second bit (i.e. encryption in progress) or
569                       ** fourth bit (i.e. encrypted partly) set in
570                       ** sysdatabases.status5
571                       */
572                       if (not ((@thisopt = @encrypted) and
573                                   ((@status5 & @encryption_in_progress = @encryption_in_progress) or
574                                       (@status5 & @encrypted_partly = @encrypted_partly))))
575                       begin
576                           if @dbdesc != ""
577                               select @dbdesc = @dbdesc + ", " + @bitdesc
578                           else select @dbdesc = @bitdesc
579   
580                           if (@thisopt = @encryption_in_progress
581                                   or @thisopt = @decryption_in_progress)
582                           begin
583                               select @dbdesc = @dbdesc + ": " + rtrim(convert(char(3), dbencryption_status('progress', @curdbid))) + "%"
584                           end
585                       end
586                   end
587   
588                   /* Turn off this status bit in the options mask */
589                   select @optmask = @optmask & ~ (@thisopt)
590               end
591   
592               /*
593               ** Get the next option bit.  Check for integer overflow for
594               ** bit 31 (0x80000000).
595               */
596               if (@thisopt < 1073741824)
597                   select @thisopt = @thisopt * 2
598               else
599                   select @thisopt = - 2147483648
600           end
601   
602           /* 
603           ** Master uses @flmode_class only to store bit names/values
604           ** but they are not applicable to master itself.
605           */
606           if @curdbid = 1
607               goto skip_flmode
608   
609           /*
610           ** Get full logging modes for this database
611           */
612           select @flmode = 0, @flmode_desc = ""
613   
614           select @flmode = object_info1
615           from master..sysattributes
616           where class = @flmode_class and object = @curdbid and attribute = 0
617   
618           if (@flmode = @flmode_all)
619           begin
620               select @flmode_desc = "full logging for all"
621               select @flmode = 0
622           end
623   
624           select @thisopt = 1
625           select @optmask = @flmode
626           while ((@optmask != 0) and (@thisopt <= @optmax))
627           begin
628               if (@optmask & @thisopt != 0)
629               begin
630                   select @bitdesc = char_value
631                   from master..sysattributes
632                   where class = @flmode_class
633                       and object = 1
634                       and object_info1 = @thisopt
635   
636                   if @bitdesc is not null
637                   begin
638                       if (@flmode_desc = "")
639                           select @flmode_desc = "full logging for "
640                               + @bitdesc
641                       else
642                           select @flmode_desc = @flmode_desc + "/"
643                               + @bitdesc
644                   end
645                   select @optmask = @optmask & ~ (@thisopt)
646               end
647   
648               if (@thisopt < 1073741824)
649                   select @thisopt = @thisopt * 2
650               else
651                   select @thisopt = - 2147483648
652           end
653   
654           if (@flmode_desc != "")
655           begin
656               if (@dbdesc != "")
657                   select @dbdesc = @dbdesc + "," + @flmode_desc
658               else
659                   select @dbdesc = @flmode_desc
660           end
661   
662   skip_flmode:
663   
664           /*
665           **  If no flags are set, say so.
666           */
667           if (@dbdesc = "")
668           begin
669               /* 17591, "no options set" */
670               exec sp_getmessage 17591, @dbdesc out
671           end
672   
673           /*
674           **  Save the description.
675           */
676           update #spdbdesc
677           set dbdesc = @dbdesc
678           from #spdbdesc
679           where dbid = @curdbid
680   
681           /*
682           **  Now get the next, if any dbid.
683           */
684           select @curdbid = min(dbid)
685           from #spdbdesc
686           where dbid > @curdbid
687       end
688   
689       /* 
690       ** Get the rows of interest from sysusages into a temp table.  This is to
691       ** avoid deadlocking with create table, which could happen if we directly
692       ** join sysdatabases and sysusages.
693       ** Mark as log only those fragments that have segmap = 0 but location
694       ** set as read only log. They are an intermediate step in the
695       ** log shrink process.
696       */
697       select u.dbid, segmap = case when u.location = 6 and u.segmap = 0
698               then 4 else u.segmap
699           end,
700           u.lstart, u.size, u.vdevno,
701           u.unreservedpgs, u.crdate
702       into #spdbusages
703       from #spdbdesc, master.dbo.sysusages u
704       where #spdbdesc.dbid = u.dbid
705           and u.vdevno >= 0
706   
707   
708       /*	
709       ** Compute number of Pages in a Megabyte.
710       */
711       declare @numpgsmb float /* Number of Pages per Megabyte */
712   
713       select @numpgsmb = (1048576. / v.low)
714       from master.dbo.spt_values v
715       where v.number = 1
716           and v.type = "E"
717   
718       /*
719       **  Now #spdbdesc is complete so we can print out the db info
720       */
721   
722       select distinct name = d.name,
723           db_size = str(sum(u.size) / @numpgsmb, 10, 1)
724           + " MB",
725           owner = suser_name(d.suid),
726           dbid = d.dbid,
727           created = convert(char(18), d.crdate, 107),
728           durability = db_attr(d.dbid, 'durability'),
729           lobcomplvl = d.lobcomp_lvl,
730           d.inrowlen,
731           status = #spdbdesc.dbdesc
732   
733       into #sphelpdb1rs
734       from master.dbo.sysdatabases d,
735           #spdbusages u, #spdbdesc
736       where d.dbid = #spdbdesc.dbid
737           and #spdbdesc.dbid = u.dbid
738       group by #spdbdesc.dbid
739       having d.dbid = #spdbdesc.dbid
740           and #spdbdesc.dbid = u.dbid
741   
742   
743       /*
744       ** Print the owner instance name only if the database name is specified
745       ** and the database is a local tempdb (@showinstance = 1)
746       */
747       select @select_list = "name, db_size, owner, dbid, created, durability, lobcomplvl, inrowlen, status"
748   
749       exec sp_autoformat @fulltabname = #sphelpdb1rs
750           , @selectlist = @select_list
751           , @orderby = "order by 1"
752   
753   
754       /*  
755       ** Print sysattributes data if there is any.  The join with multiple
756       ** instances of sysattributes is to get the string descriptions for
757       ** the class (master..sysattributes cn) and the attribute
758       ** (master..sysattributes an). These should never be longer than
759       ** 30 characters, so it's okay to truncate them.
760       */
761   
762       select name = db.name, attribute_class =
763           convert(varchar(512), cn.char_value),
764           attribute = convert(varchar(512), an.char_value), a.int_value,
765           char_value = convert(varchar(512), a.char_value), a.comments,
766           class = a.class,
767           attribute_id = a.attribute
768       into #spdbattr
769       from master.dbo.sysdatabases db, #spdbdesc d,
770           master.dbo.sysattributes a, master.dbo.sysattributes an,
771           master.dbo.sysattributes cn
772       where db.dbid = d.dbid
773           and a.class != @flmode_class
774           and a.class = cn.object
775           and a.attribute = an.object_info1
776           and a.class = an.object
777           and a.object_type = "D"
778           and a.object = d.dbid
779           and cn.class = 0
780           and cn.attribute = 0
781           and an.class = 0
782           and an.attribute = 1
783           and a.object = db.dbid
784   
785       /*
786       ** It's possible a cache is deleted without doing an unbind first. After
787       ** a server reboot the binding is marked 'invalid' (int_value = 0).
788       ** If we have such an invalid binding, don't show it in the output.
789       */
790       delete from #spdbattr
791       where class = 3
792           and attribute_id = 0
793           and int_value = 0
794   
795       if exists (select * from #spdbattr)
796       begin
797           exec sp_autoformat @fulltabname = #spdbattr,
798               @selectlist = "name, attribute_class, attribute, int_value,char_value,comments"
799       end
800   
801   
802   
803       if @showdev = 1
804       begin -- {
805           select @curdbid = dbid /* database ID */
806           from master.dbo.sysdatabases
807           where name like @dbname
808           select @pagekb = (low / 1024) /* kbytes per page */
809           from master.dbo.spt_values
810           where number = 1
811               and type = 'E'
812   
813           /* 17714, "not applicable" */
814           select @na_phrase = description
815           from master.dbo.sysmessages
816           where error = 17714
817               and isnull(langid, 0) = @sptlang
818   
819           /* Check the length of the usage column */
820           select distinct @len3 = max(datalength(m.description))
821           from master.dbo.sysdatabases d, #spdbusages u, master.dbo.sysdevices v,
822               master.dbo.spt_values b, master.dbo.sysmessages m
823           where d.dbid = u.dbid
824               and u.vdevno = v.vdevno
825               and ((v.status & 2 = 2) or (v.status2 & 8 = 8))
826               and d.name like @dbname
827               and b.type = "S"
828               and u.segmap & 7 = b.number
829               and b.msgnum = m.error
830               and isnull(m.langid, 0) = @sptlang
831   
832           /*
833           ** Order the device fragments output by sysusages.lstart unless
834           ** the 2nd parameter of sp_helpdb is "device_name"
835           */
836           if (@order = "device_name")
837           begin
838               select @order = "v.name"
839           end
840           else
841           begin
842               if (@order != "lstart")
843               begin
844                   /*
845                   ** 17600, "sp_helpdb: order value '%1!' is not valid. 
846                   ** Valid values are 'lstart' and 'device_name'. 
847                   ** Using default value 'lstart'."
848                   */
849                   raiserror 17600, @order
850               end
851   
852               /* Fragment order same as order in create/alter database */
853               select @order = "u.lstart"
854   
855           end
856   
857           if (@len3 < 20)
858               select @len3 = 20
859   
860           select @q = substring('''', 1, 1)
861   
862           select @sqlbuf =
863               'select device_fragments = v.name, size =
864   			str(size / ' + str(@numpgsmb, 10, 1) + ', 10, 1) + '
865               + @q + ' MB' + @q + ',
866   		usage = convert(char('
867               + convert(varchar, @len3) + '), m.description),
868   		created = convert(char(25), u.crdate, 100),
869   		case
870   			when u.segmap = 4 then ' + @q + @na_phrase + @q
871               + ' else 
872   			str((convert(bigint, curunreservedpgs(d.dbid, u.lstart,
873   				u.unreservedpgs)) * '
874               + convert(varchar, @pagekb) + '), 16)
875   		end "free kbytes"
876   	    from master.dbo.sysdatabases d,
877   		 #spdbusages u,
878   		 master.dbo.sysdevices v,
879   		 master.dbo.spt_values b,
880   		 master.dbo.sysmessages m
881   		where d.dbid = u.dbid
882   			and u.vdevno = v.vdevno
883   			and ((v.status & 2 = 2)  or (v.status2 & 8 = 8))
884   			and d.name = ' + @q + @dbname + @q + '
885   			and b.type = ' + @q + 'S' + @q + '
886   			and u.segmap & 7 = b.number
887   			and b.msgnum = m.error
888   			and isnull(m.langid, 0) = '
889               + convert(varchar, @sptlang) +
890               ' order by ' + @order
891   
892           exec (@sqlbuf)
893   
894           /* 
895           ** If log segment free space wasn't selected above, select it now.
896           ** Skip this step if the database is in load (32), not recovered (64)
897           ** in bypass recovery (128), suspect (256), offline (64)
898           */
899           select @sqlbuf = ""
900           if exists (select *
901                   from master.dbo.sysdatabases d, master.dbo.sysusages u
902                   where d.name like @dbname
903                       and d.dbid = u.dbid
904                       and u.segmap = 4
905                       and (d.status & (32 + 64 + 128 + 256) = 0)
906                       and (d.status2 & 64) = 0)
907   
908           begin
909               /* 17111, "log only".  Length 17 is for French, the longest */
910               select @sqlbuf = substring((select description
911                           from master.dbo.sysmessages
912                           where error = 17111
913                               and isnull(langid, 0) = @sptlang), 1, 17)
914                   + " " + "free kbytes" + " = "
915                   + convert(char, (lct_admin("logsegment_freepages", @curdbid)
916                   - lct_admin("reserved_for_rollbacks", @curdbid))
917                   * @pagekb)
918           end
919   
920           /* 
921           ** If there are holes in the database, show the size. 
922           ** - LOG holes have sysusages.location == 9
923           ** - DATA holes have sysusages.location == 10
924           */
925           select @location = 9
926   
927           while (@location > 0)
928           begin
929               select @holesize = sum(size)
930               from master.dbo.sysusages
931               where dbid = @curdbid
932                   and vdevno = - @curdbid
933                   and location = @location
934   
935               if (@holesize > 0)
936               begin
937                   if (@sqlbuf != "")
938                       select @sqlbuf = rtrim(@sqlbuf) + ", "
939   
940                   /* 17909, "only unavailable" */
941                   select @sqlbuf = @sqlbuf
942                       + case @location when 9 then "log " else "data " end
943                       + substring((select description
944                               from master.dbo.sysmessages
945                               where error = 17909
946                                   and isnull(langid, 0) = @sptlang), 1, 20)
947                       + " kbytes = " + convert(char, @holesize * @pagekb)
948               end
949   
950               if (@location = 9)
951                   select @location = 10
952               else
953                   select @location = 0
954           end
955   
956           if (@sqlbuf != "")
957               select substring(@sqlbuf, 1, 110)
958   
959           /*
960           **  If there is only one database and we are in it, show the
961           **  segments.
962           */
963           if exists (select *
964                   from #spdbdesc
965                   where db_id() = dbid)
966           begin -- {
967               declare @curdevice varchar(255),
968                   @curseg smallint,
969                   @segbit int
970   
971               delete #spdbdesc
972   
973               select @curdevice = min(d.name)
974               from #spdbusages u, master.dbo.sysdevices d
975               where u.dbid = db_id()
976                   and u.vdevno = d.vdevno
977                   and ((d.status & 2 = 2) or (d.status2 & 8 = 8))
978               while (@curdevice is not null)
979               begin -- {
980                   /*
981                   ** We need an inner loop here to go through
982                   **  all the possible segment.
983                   */
984                   select @curseg = min(segment)
985                   from syssegments
986                   while (@curseg is not null)
987                   begin
988                       if (@curseg < 31)
989                           select @segbit = power(2, @curseg)
990                       else select @segbit = low
991                           from master.dbo.spt_values
992                           where type = "E"
993                               and number = 2
994                       insert into #spdbdesc
995                       select @curseg, @curdevice
996                       from #spdbusages u,
997                           master.dbo.sysdevices d,
998                           master.dbo.spt_values v
999                       where u.segmap & @segbit = @segbit
1000                          and u.vdevno = d.vdevno
1001                          and u.dbid = db_id()
1002                          and ((d.status & 2 = 2) or (d.status2 & 8 = 8))
1003                          and v.number = 1
1004                          and v.type = "E"
1005                          and d.name = @curdevice
1006                      select @curseg = min(segment)
1007                      from syssegments
1008                      where segment > @curseg
1009                  end
1010  
1011                  select @curdevice = min(d.name)
1012                  from #spdbusages u,
1013                      master.dbo.sysdevices d
1014                  where u.dbid = db_id()
1015                      and u.vdevno = d.vdevno
1016                      and ((d.status & 2 = 2) or (d.status2 & 8 = 8))
1017                      and d.name > @curdevice
1018              end -- }
1019  
1020              /*
1021              **  One last check for any devices that have no segments.
1022              */
1023              insert into #spdbdesc
1024              select null, d.name
1025              from #spdbusages u,
1026                  master.dbo.sysdevices d
1027              where u.segmap = 0
1028                  and u.vdevno = d.vdevno
1029                  and u.dbid = db_id()
1030                  and ((d.status & 2 = 2) or (d.status2 & 8 = 8))
1031  
1032              /* 17592, " -- unused by any segments --" */
1033              exec sp_getmessage 17592, @msg out
1034  
1035              select distinct device = dbdesc,
1036                  segment = isnull(name, @msg)
1037              into #sphelpdb2rs
1038              from #spdbdesc, syssegments
1039              where dbid *= segment
1040              exec sp_autoformat @fulltabname = #sphelpdb2rs,
1041                  @orderby = "order by 1, 2"
1042              drop table #sphelpdb2rs
1043          end -- }
1044  
1045          /*
1046          ** If the given database is a template database then
1047          ** print all the database created from it.
1048          */
1049          select @dbstatus4 = d.status4
1050          from master.dbo.sysdatabases d
1051          where name like @dbname
1052  
1053          /*
1054          ** OMNI: Display the default location for remote tables
1055          **       if one exists.
1056          **
1057          ** IMDB: In case of in-memory database def_remote_loc is
1058          ** used to store the template database associated with the
1059          ** in-memory database.
1060          */
1061          if exists (select *
1062                  from master.dbo.sysdatabases
1063                  where name like @dbname
1064                      and def_remote_loc is not null)
1065          begin --{
1066              if ((@dbstatus4 & @has_template_mask) != 0)
1067              begin --{
1068                  select "template_database"
1069                      = substring(def_remote_loc, 1, 30)
1070                  from master.dbo.sysdatabases
1071                  where name like @dbname
1072              end --}
1073              else
1074              begin --{
1075                  select "remote location" =
1076                      substring(def_remote_loc, 1, 77)
1077                  from master.dbo.sysdatabases
1078                  where name like @dbname
1079              end --}
1080          end --}
1081  
1082          if ((@dbstatus4 & @template_mask) = @template_mask)
1083          begin --{
1084              select template_for = name
1085              into #templateddbs
1086              from master.dbo.sysdatabases
1087              where def_remote_loc like @dbname
1088  
1089              exec sp_autoformat @fulltabname = #templateddbs
1090          end --}
1091      end -- }
1092  
1093  
1094  
1095      drop table #spdbdesc
1096      drop table #spdbattr
1097      return (0)
1098  


exec sp_procxmode 'sp_helpdb', 'AnyMode'
go

Grant Execute on sp_helpdb to public
go
RESULT SETS
sp_helpdb_rset_003
sp_helpdb_rset_002
sp_helpdb_rset_001

DEFECTS
 QBGB 6 Bad group by d.name 722
 QCAR 6 Cartesian product between tables #spdbusages u and [master..spt_values v] 996
 QJWI 5 Join or Sarg Without Index 311
 QJWI 5 Join or Sarg Without Index 356
 QJWI 5 Join or Sarg Without Index 401
 QJWI 5 Join or Sarg Without Index 510
 QJWI 5 Join or Sarg Without Index 560
 MEST 4 Empty String will be replaced by Single Space 289
 MEST 4 Empty String will be replaced by Single Space 317
 MEST 4 Empty String will be replaced by Single Space 362
 MEST 4 Empty String will be replaced by Single Space 467
 MEST 4 Empty String will be replaced by Single Space 516
 MEST 4 Empty String will be replaced by Single Space 576
 MEST 4 Empty String will be replaced by Single Space 612
 MEST 4 Empty String will be replaced by Single Space 638
 MEST 4 Empty String will be replaced by Single Space 654
 MEST 4 Empty String will be replaced by Single Space 656
 MEST 4 Empty String will be replaced by Single Space 667
 MEST 4 Empty String will be replaced by Single Space 899
 MEST 4 Empty String will be replaced by Single Space 937
 MEST 4 Empty String will be replaced by Single Space 956
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 MINU 4 Unique Index with nullable columns master..sysmessages master..sysmessages
 MTYP 4 Assignment type mismatch @bitdesc: varchar(50) = varchar(1024) 305
 MTYP 4 Assignment type mismatch @bitdesc: varchar(50) = varchar(1024) 350
 MTYP 4 Assignment type mismatch @bitdesc: varchar(50) = varchar(1024) 395
 MTYP 4 Assignment type mismatch @str1: varchar(30) = varchar(1024) 430
 MTYP 4 Assignment type mismatch @str2: varchar(30) = varchar(1024) 438
 MTYP 4 Assignment type mismatch @bitdesc: varchar(50) = varchar(1024) 504
 MTYP 4 Assignment type mismatch @bitdesc: varchar(50) = varchar(1024) 554
 MTYP 4 Assignment type mismatch @bitdesc: varchar(50) = varchar(768) 630
 MTYP 4 Assignment type mismatch @na_phrase: varchar(30) = varchar(1024) 814
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 749
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 797
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 1040
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 1089
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
121
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
128
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
135
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
142
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
149
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
199
 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]
774
 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]
775
 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: [attribute, object_info1, object_info2, object_info3, object_cinfo]
778
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 80
 QTYP 4 Comparison type mismatch smallint = int 80
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 86
 QTYP 4 Comparison type mismatch smallint = int 86
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 272
 QTYP 4 Comparison type mismatch smallint = int 272
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 276
 QTYP 4 Comparison type mismatch smallint = int 276
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 616
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 616
 QTYP 4 Comparison type mismatch smallint = int 616
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 632
 QTYP 4 Comparison type mismatch smallint = int 632
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 697
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 773
 QTYP 4 Comparison type mismatch smallint = int 773
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 774
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 775
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 776
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 778
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 779
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 780
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 781
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 782
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 783
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 791
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 792
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 932
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 933
 QTYP 4 Comparison type mismatch smallint = int 933
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 965
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 975
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1001
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1014
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1029
 TNOI 4 Table with no index sybsystemprocs..syssegments sybsystemprocs..syssegments
 TNOU 4 Table with no unique index master..spt_values master..spt_values
 MDYN 3 Proc uses Dynamic SQL but is not flagged with Dynamic Ownership Chain 19
 MGTP 3 Grant to public master..spt_values  
 MGTP 3 Grant to public master..sysattributes  
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public master..sysdevices  
 MGTP 3 Grant to public master..sysmessages  
 MGTP 3 Grant to public master..sysusages  
 MGTP 3 Grant to public sybsystemprocs..sp_helpdb  
 MGTP 3 Grant to public sybsystemprocs..syssegments  
 MNER 3 No Error Check should check @@error after insert 251
 MNER 3 No Error Check should check return value of exec 670
 MNER 3 No Error Check should check @@error after update 676
 MNER 3 No Error Check should check @@error after select into 697
 MNER 3 No Error Check should check @@error after select into 722
 MNER 3 No Error Check should check return value of exec 749
 MNER 3 No Error Check should check @@error after select into 762
 MNER 3 No Error Check should check @@error after delete 790
 MNER 3 No Error Check should check return value of exec 797
 MNER 3 No Error Check should check @@error after delete 971
 MNER 3 No Error Check should check @@error after insert 994
 MNER 3 No Error Check should check @@error after insert 1023
 MNER 3 No Error Check should check return value of exec 1033
 MNER 3 No Error Check should check @@error after select into 1035
 MNER 3 No Error Check should check return value of exec 1040
 MNER 3 No Error Check should check @@error after select into 1084
 MNER 3 No Error Check should check return value of exec 1089
 MUCO 3 Useless Code Useless Brackets 111
 MUCO 3 Useless Code Useless Brackets 123
 MUCO 3 Useless Code Useless Brackets 130
 MUCO 3 Useless Code Useless Brackets 137
 MUCO 3 Useless Code Useless Brackets 144
 MUCO 3 Useless Code Useless Brackets 151
 MUCO 3 Useless Code Useless Brackets 296
 MUCO 3 Useless Code Useless Brackets 301
 MUCO 3 Useless Code Useless Brackets 330
 MUCO 3 Useless Code Useless Brackets 341
 MUCO 3 Useless Code Useless Brackets 346
 MUCO 3 Useless Code Useless Brackets 375
 MUCO 3 Useless Code Useless Brackets 386
 MUCO 3 Useless Code Useless Brackets 391
 MUCO 3 Useless Code Useless Brackets 406
 MUCO 3 Useless Code Useless Brackets 428
 MUCO 3 Useless Code Useless Brackets 453
 MUCO 3 Useless Code Useless Brackets 472
 MUCO 3 Useless Code Useless Brackets 484
 MUCO 3 Useless Code Useless Brackets 495
 MUCO 3 Useless Code Useless Brackets 500
 MUCO 3 Useless Code Useless Brackets 532
 MUCO 3 Useless Code Useless Brackets 534
 MUCO 3 Useless Code Useless Brackets 545
 MUCO 3 Useless Code Useless Brackets 550
 MUCO 3 Useless Code Useless Brackets 572
 MUCO 3 Useless Code Useless Brackets 580
 MUCO 3 Useless Code Useless Brackets 596
 MUCO 3 Useless Code Useless Brackets 618
 MUCO 3 Useless Code Useless Brackets 626
 MUCO 3 Useless Code Useless Brackets 628
 MUCO 3 Useless Code Useless Brackets 638
 MUCO 3 Useless Code Useless Brackets 648
 MUCO 3 Useless Code Useless Brackets 654
 MUCO 3 Useless Code Useless Brackets 656
 MUCO 3 Useless Code Useless Brackets 667
 MUCO 3 Useless Code Useless Brackets 713
 MUCO 3 Useless Code Useless Brackets 808
 MUCO 3 Useless Code Useless Brackets 836
 MUCO 3 Useless Code Useless Brackets 842
 MUCO 3 Useless Code Useless Brackets 857
 MUCO 3 Useless Code Useless Brackets 927
 MUCO 3 Useless Code Useless Brackets 935
 MUCO 3 Useless Code Useless Brackets 937
 MUCO 3 Useless Code Useless Brackets 950
 MUCO 3 Useless Code Useless Brackets 956
 MUCO 3 Useless Code Useless Brackets 978
 MUCO 3 Useless Code Useless Brackets 986
 MUCO 3 Useless Code Useless Brackets 988
 MUCO 3 Useless Code Useless Brackets 1066
 MUCO 3 Useless Code Useless Brackets 1082
 MUCO 3 Useless Code Useless Brackets 1097
 MUUF 3 Update or Delete with Useless From Clause 676
 QAFM 3 Var Assignment from potentially many rows 119
 QAFM 3 Var Assignment from potentially many rows 126
 QAFM 3 Var Assignment from potentially many rows 133
 QAFM 3 Var Assignment from potentially many rows 140
 QAFM 3 Var Assignment from potentially many rows 147
 QAFM 3 Var Assignment from potentially many rows 197
 QAFM 3 Var Assignment from potentially many rows 270
 QAFM 3 Var Assignment from potentially many rows 430
 QAFM 3 Var Assignment from potentially many rows 438
 QAFM 3 Var Assignment from potentially many rows 614
 QAFM 3 Var Assignment from potentially many rows 630
 QAFM 3 Var Assignment from potentially many rows 713
 QAFM 3 Var Assignment from potentially many rows 808
 QAFM 3 Var Assignment from potentially many rows 814
 QAFM 3 Var Assignment from potentially many rows 990
 QCRS 3 Conditional Result Set 957
 QCRS 3 Conditional Result Set 1068
 QCRS 3 Conditional Result Set 1075
 QCTC 3 Conditional Table Creation 1035
 QCTC 3 Conditional Table Creation 1084
 QDIS 3 Check correct use of 'select distinct' 722
 QDIS 3 Check correct use of 'select distinct' 820
 QDIS 3 Check correct use of 'select distinct' 1035
 QGWO 3 Group by/Distinct/Union without order by 722
 QGWO 3 Group by/Distinct/Union without order by 820
 QGWO 3 Group by/Distinct/Union without order by 1035
 QISO 3 Set isolation level 71
 QIWC 3 Insert with not all columns specified missing 1 columns out of 2 251
 QJWT 3 Join or Sarg Without Index on temp table 737
 QJWT 3 Join or Sarg Without Index on temp table 740
 QJWT 3 Join or Sarg Without Index on temp table 1039
 QNAJ 3 Not using ANSI Inner Join 306
 QNAJ 3 Not using ANSI Inner Join 351
 QNAJ 3 Not using ANSI Inner Join 396
 QNAJ 3 Not using ANSI Inner Join 431
 QNAJ 3 Not using ANSI Inner Join 439
 QNAJ 3 Not using ANSI Inner Join 505
 QNAJ 3 Not using ANSI Inner Join 555
 QNAJ 3 Not using ANSI Inner Join 703
 QNAJ 3 Not using ANSI Inner Join 734
 QNAJ 3 Not using ANSI Inner Join 769
 QNAJ 3 Not using ANSI Inner Join 821
 QNAJ 3 Not using ANSI Inner Join 901
 QNAJ 3 Not using ANSI Inner Join 974
 QNAJ 3 Not using ANSI Inner Join 996
 QNAJ 3 Not using ANSI Inner Join 1012
 QNAJ 3 Not using ANSI Inner Join 1025
 QNAM 3 Select expression has no name substring(@sqlbuf, 1, 110) 957
 QNAO 3 Not using ANSI Outer Join 1038
 QNCO 3 No column in result set 995
 QNUA 3 Should use Alias: Table #spdbdesc 703
 QNUA 3 Should use Alias: Table #spdbdesc 735
 QNUA 3 Should use Alias: Column dbdesc should use alias #spdbdesc 1035
 QNUA 3 Should use Alias: Column name should use alias syssegments 1036
 QNUA 3 Should use Alias: Table #spdbdesc 1038
 QNUA 3 Should use Alias: Table sybsystemprocs..syssegments 1038
 QNUA 3 Should use Alias: Column dbid should use alias #spdbdesc 1039
 QNUA 3 Should use Alias: Column segment should use alias syssegments 1039
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid}
78
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid}
84
 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, class, attribute}
272
 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, class, attribute}
276
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error}
Uncovered: [dlevel]
313
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error}
Uncovered: [dlevel]
358
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error}
Uncovered: [dlevel]
403
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error}
Uncovered: [dlevel, langid]
436
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error}
Uncovered: [dlevel, langid]
444
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error}
Uncovered: [dlevel]
512
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error}
Uncovered: [dlevel]
562
 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, class, attribute}
616
 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, class, object_info1}
632
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
Uncovered: [lstart]
704
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.ncsysusages unique
(vdevno, vstart)
Intersection: {vdevno}
705
 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]
774
 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]
775
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid}
816
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error}
Uncovered: [dlevel]
829
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid}
912
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
931
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid}
945
 QSWV 3 Sarg with variable @thisopt, Candidate Index: spt_values.spt_valuesclust clustered(number, type) F 312
 QSWV 3 Sarg with variable @thisopt, Candidate Index: spt_values.spt_valuesclust clustered(number, type) F 357
 QSWV 3 Sarg with variable @thisopt, Candidate Index: spt_values.spt_valuesclust clustered(number, type) F 402
 QSWV 3 Sarg with variable @thisopt, Candidate Index: spt_values.spt_valuesclust clustered(number, type) F 511
 QSWV 3 Sarg with variable @thisopt, Candidate Index: spt_values.spt_valuesclust clustered(number, type) F 561
 QTJ1 3 Table only appears in inner join clause 703
 VNRD 3 Variable is not read @imdb_mask 218
 VNRD 3 Variable is not read @pgcomp_mask 219
 VNRD 3 Variable is not read @rowcomp_mask 220
 VUNU 3 Variable is not used @len1 48
 VUNU 3 Variable is not used @len2 48
 VUNU 3 Variable is not used @instancename 50
 VUNU 3 Variable is not used @is_sidb 65
 MDYS 2 Dynamic SQL Marker 892
 MRST 2 Result Set Marker 957
 MRST 2 Result Set Marker 1068
 MRST 2 Result Set Marker 1075
 MSUB 2 Subquery Marker 77
 MSUB 2 Subquery Marker 83
 MSUB 2 Subquery Marker 900
 MSUB 2 Subquery Marker 910
 MSUB 2 Subquery Marker 943
 MSUB 2 Subquery Marker 963
 MSUB 2 Subquery Marker 1061
 MTR1 2 Metrics: Comments Ratio Comments: 30% 19
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 172 = 172dec - 2exi + 2 19
 MTR3 2 Metrics: Query Complexity Complexity: 567 19
 PRED_QUERY_COLLECTION 2 {m=master..sysmessages, sv=master..spt_values} 0 430
 PRED_QUERY_COLLECTION 2 {m=master..sysmessages, sv=master..spt_values} 0 438
 PRED_QUERY_COLLECTION 2 {d=master..sysdatabases, u=master..sysusages} 0 900

DATA PROPAGATION detailed
ColumnWritten To
@dbnamesp_helpdb_rset_003.remote location
@ordersp_helpdb_rset_003.remote location

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_getmessage  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..syslanguages (1)  
   reads table sybsystemprocs..sysusermessages  
   reads table master..sysmessages (1)  
read_writes table tempdb..#spdbattr (1) 
writes table sybsystemprocs..sp_helpdb_rset_001 
read_writes table tempdb..#spdbdesc (1) 
reads table master..sysusages (1)  
reads table sybsystemprocs..syssegments  
reads table master..spt_values (1)  
writes table sybsystemprocs..sp_helpdb_rset_003 
writes table tempdb..#sphelpdb2rs (1) 
calls proc sybsystemprocs..sp_autoformat  
   writes table sybsystemprocs..sp_autoformat_rset_002 
   writes table sybsystemprocs..sp_autoformat_rset_005 
   calls proc sybsystemprocs..sp_namecrack  
   read_writes table tempdb..#colinfo_af (1) 
   writes table sybsystemprocs..sp_autoformat_rset_001 
   writes table sybsystemprocs..sp_autoformat_rset_004 
   reads table master..syscolumns (1)  
   reads table tempdb..systypes (1)  
   reads table master..systypes (1)  
   calls proc sybsystemprocs..sp_autoformat  
   reads table tempdb..syscolumns (1)  
   writes table sybsystemprocs..sp_autoformat_rset_003 
reads table master..sysattributes (1)  
reads table master..sysmessages (1)  
reads table master..sysdatabases (1)  
writes table tempdb..#templateddbs (1) 
read_writes table tempdb..#spdbusages (1) 
reads table master..sysdevices (1)  
writes table sybsystemprocs..sp_helpdb_rset_002 
writes table tempdb..#sphelpdb1rs (1)