DatabaseProcApplicationCreatedLinks
sybsystemprocssp_helpthreshold  31 Aug 14Defects Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     
4     /*
5     ** Messages for "sp_helpthreshold"     17910
6     **
7     ** 17910, "Database '%1!' has no thresholds -- table '%2!' does not exist."
8     ** 17911, "Segment '%1!' does not exist."
9     */
10    
11    create procedure sp_helpthreshold
12        @segname varchar(255) = NULL
13    as
14    
15        declare @dbname varchar(255)
16    
17        select @dbname = db_name()
18    
19        if @@trancount = 0
20        begin
21            set chained off
22        end
23    
24        set transaction isolation level 1
25    
26        /*
27        ** Make sure our database is recent enough to contain Systhresholds
28        */
29        if (select name from sysobjects
30                where name = 'systhresholds'
31                    and type = 'S')
32            is NULL
33        begin
34            /*
35            ** 17910, "Database '%1!' has no thresholds -- table '%2!' does not
36            **	  exist."
37            */
38            raiserror 17910, @dbname, "systhresholds"
39            return (1)
40        end
41    
42        /*
43        ** If user wants a specific segment's thresholds, show those.
44        */
45        if (@segname is not NULL)
46        begin
47            if (select segment from syssegments where name = @segname) is NULL
48            begin
49                /*
50                ** 17911, "Segment '%1!' does not exist."
51                */
52                raiserror 17911, @segname
53                return (2)
54            end
55    
56            /*
57            ** Get the threshold information for all the user-defined ones.
58            */
59            select segment_name = s.name,
60                free_pages = t.free_space,
61                last_chance = t.status,
62                threshold_procedure = t.proc_name
63            into #sphelpthreshold1rs
64            from syssegments s, systhresholds t
65            where s.segment = t.segment
66                and s.name = @segname
67                and t.status != 1
68    
69            UNION
70    
71            /*
72            ** Get the threshold information for the last chance threshold
73            */
74            select segment_name = s.name,
75                free_pages = lct_admin("reserve", 0),
76                last_chance = t.status,
77                threshold_procedure = t.proc_name
78            from syssegments s, systhresholds t
79            where s.segment = t.segment
80                and s.name = @segname
81                and t.status = 1
82            exec sp_autoformat @fulltabname = #sphelpthreshold1rs,
83                @selectlist = "'segment name' = segment_name,'free pages' = free_pages, 'last chance?' = last_chance, 'threshold procedure' = threshold_procedure"
84            drop table #sphelpthreshold1rs
85            return (0)
86        end
87    
88        /*
89        ** Show all thresholds for all segments.
90        */
91        select segment_name = s.name,
92            free_pages = t.free_space,
93            last_chance = t.status,
94            threshold_procedure = t.proc_name
95        into #sphelpthreshold2rs
96        from syssegments s, systhresholds t
97        where s.segment = t.segment
98            and t.status != 1
99    
100       UNION
101   
102       select segment_name = s.name,
103           free_pages = lct_admin("reserve", 0),
104           last_chance = t.status,
105           threshold_procedure = t.proc_name
106       from syssegments s, systhresholds t
107       where s.segment = t.segment
108           and t.status = 1
109       exec sp_autoformat @fulltabname = #sphelpthreshold2rs,
110           @selectlist = "'segment name' = segment_name,'free pages' = free_pages, 'last chance?' = last_chance, 'threshold procedure' = threshold_procedure"
111       drop table #sphelpthreshold2rs
112       return (0)
113   


exec sp_procxmode 'sp_helpthreshold', 'AnyMode'
go

Grant Execute on sp_helpthreshold to public
go
DEFECTS
 QJWI 5 Join or Sarg Without Index 65
 QJWI 5 Join or Sarg Without Index 79
 QJWI 5 Join or Sarg Without Index 97
 QJWI 5 Join or Sarg Without Index 107
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 82
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 109
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 67
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 81
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 98
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 108
 TNOI 4 Table with no index sybsystemprocs..syssegments sybsystemprocs..syssegments
 TNOI 4 Table with no index sybsystemprocs..systhresholds sybsystemprocs..systhresholds
 MGTP 3 Grant to public sybsystemprocs..sp_helpthreshold  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MGTP 3 Grant to public sybsystemprocs..syssegments  
 MGTP 3 Grant to public sybsystemprocs..systhresholds  
 MNER 3 No Error Check should check @@error after select into 59
 MNER 3 No Error Check should check return value of exec 82
 MNER 3 No Error Check should check @@error after select into 91
 MNER 3 No Error Check should check return value of exec 109
 MUCO 3 Useless Code Useless Brackets 39
 MUCO 3 Useless Code Useless Brackets 45
 MUCO 3 Useless Code Useless Brackets 53
 MUCO 3 Useless Code Useless Brackets 85
 MUCO 3 Useless Code Useless Brackets 112
 QCTC 3 Conditional Table Creation 59
 QGWO 3 Group by/Distinct/Union without order by 59
 QGWO 3 Group by/Distinct/Union without order by 91
 QISO 3 Set isolation level 24
 QNAJ 3 Not using ANSI Inner Join 64
 QNAJ 3 Not using ANSI Inner Join 78
 QNAJ 3 Not using ANSI Inner Join 96
 QNAJ 3 Not using ANSI Inner Join 106
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {name}		 30
 QUNI 3 Check Use of 'union' vs 'union all' 59
 QUNI 3 Check Use of 'union' vs 'union all' 91
 MSUB 2 Subquery Marker 29
 MSUB 2 Subquery Marker 47
 MTR1 2 Metrics: Comments Ratio Comments: 23% 11
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 9 = 11dec - 4exi + 2 11
 MTR3 2 Metrics: Query Complexity Complexity: 50 11
 PRED_QUERY_COLLECTION 2 {s=sybsystemprocs..syssegments, t=sybsystemprocs..systhresholds} 0 59
 PRED_QUERY_COLLECTION 2 {s=sybsystemprocs..syssegments, t=sybsystemprocs..systhresholds} 0 74
 PRED_QUERY_COLLECTION 2 {s=sybsystemprocs..syssegments, t=sybsystemprocs..systhresholds} 0 91
 PRED_QUERY_COLLECTION 2 {s=sybsystemprocs..syssegments, t=sybsystemprocs..systhresholds} 0 102

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_autoformat  
   reads table tempdb..syscolumns (1)  
   read_writes table tempdb..#colinfo_af (1) 
   calls proc sybsystemprocs..sp_autoformat  
   reads table master..syscolumns (1)  
   reads table tempdb..systypes (1)  
   reads table master..systypes (1)  
   calls proc sybsystemprocs..sp_namecrack  
reads table sybsystemprocs..sysobjects  
reads table sybsystemprocs..systhresholds  
reads table sybsystemprocs..syssegments  
writes table tempdb..#sphelpthreshold2rs (1) 
writes table tempdb..#sphelpthreshold1rs (1)