DatabaseProcApplicationCreatedLinks
sybsystemprocssp_help_resource_limit  31 Aug 14Defects Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     
4     /*
5     ** Messages for "sp_help_resource_limit"
6     **
7     ** 17231, "No login with the specified name exists."
8     ** 18207, "Illegal action %1!."
9     ** 18216, "Users other than the System Administrator (SA) may only view limits applicable to themselves."
10    ** 18217, "Illegal limit time argument '%1!'."
11    ** 18218, "Unknown limitday value '%1!'."
12    ** 
13    */
14    
15    create procedure sp_help_resource_limit
16        @name varchar(30) = NULL, /* login to which limits apply */
17        @appname varchar(30) = NULL, /* application to which limits apply */
18        @limittime varchar(30) = NULL, /* time during which limits apply */
19        @limitday varchar(30) = NULL, /* day during which limits apply */
20        @scope int = NULL, /* scope of limits */
21        @action int = NULL, /* what to do if limits are exceeded */
22        @verbose int = 0 /* 1 means describe, 0 means default */
23    as
24    
25        declare @rangeid smallint
26        declare @limittime_dt datetime
27        declare @limitdaynum int
28        declare @procval int
29        declare @has_sa_role int
30    
31        /*
32        ** Check if the user executing the procedure
33        ** has sa_role. We use show_role() because
34        ** proc_role() will raise an error message
35        ** if the role is not enabled for this user.
36        */
37        if (charindex("sa_role", show_role()) > 0)
38            select @has_sa_role = 1
39        else
40            select @has_sa_role = 0
41    
42        select @procval = 0
43    
44        /* Is login valid? */
45        if ((@name is not null) and not exists
46                    (select * from master.dbo.syslogins where name = @name and
47                        ((status & 512) != 512))) /* not LOGIN PROFILE */
48        begin
49            /*
50            ** 17231, "No login with the specified name exists."
51            */
52            raiserror 17231
53            return (1)
54        end
55    
56        if ((@has_sa_role = 0) and (@name is not null) and (@name != suser_name()))
57        begin
58            /*
59            ** 18216, "Users other than the System Administrator (SA) may only view limits applicable to themselves."
60            */
61            raiserror 18216
62            return (1)
63        end
64        else
65        begin
66            /*
67            ** Call proc_role() with each role that the user has 
68            ** in order to send the success audit records.  
69            ** Note that this could mean 1 or 2 audit records.
70            */
71            if (@has_sa_role = 1)
72                select @procval = proc_role("sa_role")
73        end
74    
75        /* Has a legal action been specified? */
76        if (@action is not null) and ((@action < 1) or (@action > 4))
77        begin
78            /*
79            ** 18207, "Illegal action %1!."
80            */
81            raiserror 18207, @action
82            return (1)
83        end
84    
85        /* Convert the limit time to datetime so we can do arithmetic */
86        if (@limittime is not null)
87        begin
88            select @limittime_dt = convert(datetime, @limittime)
89            if (@limittime_dt is null)
90            begin
91                /*
92                ** 18217, "Illegal limit time argument '%1!'."
93                */
94                raiserror 18217, @limittime
95                return (1)
96            end
97        end
98    
99        /* Convert the starting time to datetime so we can do arithmetic */
100       if (@limitday is not null)
101       begin
102           select @limitdaynum = daytonum(@limitday)
103           if (@limitdaynum = - 1)
104           begin
105               /*
106               ** 18218, "Unknown limitday value '%1!'."
107               */
108               raiserror 18218, @limitday
109               return (1)
110           end
111       end
112   
113       if (@verbose = 0)
114       begin
115           /* Find the limits that apply */
116           select l.name, l.appname, rangename = r.name, l.rangeid, l.limitid, l.limitvalue, l.enforced, l.action, l.scope
117           into #sphelpreslimit1rs
118           from master.dbo.sysresourcelimits l, master.dbo.systimeranges r
119           where (l.rangeid = r.id)
120               and
121               (
122                   (l.name = @name) or
123                   (l.name is null) or
124                   ((@has_sa_role = 1) and (@name is null)) or
125                   ((suser_name() = @name) and (l.name is null))
126               )
127               and
128               ((@appname is null) or (l.appname = @appname))
129               and
130               ((@scope is null) or (l.scope = @scope))
131               and
132               ((@action is null) or (l.action = @action))
133               and
134               (
135                   (
136                       (r.startday <= r.endday)
137                       and
138                       ((@limitday is null) or
139                           ((@limitdaynum >= r.startday) and
140                               (@limitdaynum <= r.endday)))
141                       and
142                       ((@limittime is null) or
143                           ((convert(datetime, r.starttime) <= @limittime_dt) and
144                               ((convert(datetime, r.endtime) > @limittime_dt) or
145                                   ((datepart(hour, convert(datetime, r.endtime)) = 0) and
146                                       (datepart(minute, convert(datetime, r.endtime)) = 0) and
147                                       (datepart(second, convert(datetime, r.endtime)) = 0)))))
148                   )
149                   or
150                   (
151                       (r.startday >= r.endday)
152                       and
153                       ((@limitday is null) or
154                           ((@limitdaynum >= r.startday) or
155                               (@limitdaynum <= r.endday)))
156                       and
157                       ((@limittime is null) or
158                           ((convert(datetime, r.starttime) <= @limittime_dt) and
159                               ((convert(datetime, r.endtime) > @limittime_dt) or
160                                   ((datepart(hour, convert(datetime, r.endtime)) = 0) and
161                                       (datepart(minute, convert(datetime, r.endtime)) = 0) and
162                                       (datepart(second, convert(datetime, r.endtime)) = 0)))))
163                   )
164               )
165           exec sp_autoformat @fulltabname = #sphelpreslimit1rs
166           drop table #sphelpreslimit1rs
167       end
168       else
169       begin
170           /* Find the limits that apply */
171           select l.name, l.appname, rangename = r.name, l.rangeid,
172               case l.limitid
173                   when 1 then convert(char(15),
174                   "io_cost" + "(" + ltrim(str(l.limitid)) + ")")
175                   when 2 then convert(char(15),
176                   "elapsed_time" + "(" + ltrim(str(l.limitid)) + ")")
177                   when 3 then convert(char(15),
178                   "row_count" + "(" + ltrim(str(l.limitid)) + ")")
179                   when 4 then convert(char(15),
180                   "tempdb_space" + "(" + ltrim(str(l.limitid)) + ")")
181               end AS "limitType",
182               l.limitvalue,
183               case l.enforced
184                   when 1 then convert(char(16), "Compilation Time")
185                   when 2 then convert(char(16), "Execution Time")
186                   when 3 then convert(char(16), "Any Time")
187               end AS "enforced",
188               case l.action
189                   when 0 then convert(char(17), "No Action")
190                   when 1 then convert(char(17), "Warn")
191                   when 2 then convert(char(17), "Abort Batch")
192                   when 3 then convert(char(17), "Abort Transaction")
193                   when 4 then convert(char(17), "Kill Session")
194               end AS "action",
195               case l.scope
196                   when 1 then convert(char(11), "Query")
197                   when 2 then convert(char(11), "Batch")
198                   when 4 then convert(char(11), "Transaction")
199                   when 6 then convert(char(11), "Batch/Tran")
200               end AS "scope"
201           into #sphelpreslimit1rs_v
202           from master.dbo.sysresourcelimits l, master.dbo.systimeranges r
203           where (l.rangeid = r.id)
204               and
205               (
206                   (l.name = @name) or
207                   (l.name is null) or
208                   ((@has_sa_role = 1) and (@name is null)) or
209                   ((suser_name() = @name) and (l.name is null))
210               )
211               and
212               ((@appname is null) or (l.appname = @appname))
213               and
214               ((@scope is null) or (l.scope = @scope))
215               and
216               ((@action is null) or (l.action = @action))
217               and
218               (
219                   (
220                       (r.startday <= r.endday)
221                       and
222                       ((@limitday is null) or
223                           ((@limitdaynum >= r.startday) and
224                               (@limitdaynum <= r.endday)))
225                       and
226                       ((@limittime is null) or
227                           ((convert(datetime, r.starttime) <= @limittime_dt) and
228                               ((convert(datetime, r.endtime) > @limittime_dt) or
229                                   ((datepart(hour, convert(datetime, r.endtime)) = 0) and
230                                       (datepart(minute, convert(datetime, r.endtime)) = 0) and
231                                       (datepart(second, convert(datetime, r.endtime)) = 0)))))
232                   )
233                   or
234                   (
235                       (r.startday >= r.endday)
236                       and
237                       ((@limitday is null) or
238                           ((@limitdaynum >= r.startday) or
239                               (@limitdaynum <= r.endday)))
240                       and
241                       ((@limittime is null) or
242                           ((convert(datetime, r.starttime) <= @limittime_dt) and
243                               ((convert(datetime, r.endtime) > @limittime_dt) or
244                                   ((datepart(hour, convert(datetime, r.endtime)) = 0) and
245                                       (datepart(minute, convert(datetime, r.endtime)) = 0) and
246                                       (datepart(second, convert(datetime, r.endtime)) = 0)))))
247                   )
248               )
249           exec sp_autoformat @fulltabname = #sphelpreslimit1rs_v
250           drop table #sphelpreslimit1rs_v
251   
252       end
253   
254       return (0)
255   
256   


exec sp_procxmode 'sp_help_resource_limit', 'AnyMode'
go

Grant Execute on sp_help_resource_limit to public
go
DEFECTS
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 165
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 249
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 130
 QTYP 4 Comparison type mismatch tinyint = int 130
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 132
 QTYP 4 Comparison type mismatch tinyint = int 132
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs tinyint 139
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs tinyint 140
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs tinyint 154
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs tinyint 155
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 214
 QTYP 4 Comparison type mismatch tinyint = int 214
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 216
 QTYP 4 Comparison type mismatch tinyint = int 216
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs tinyint 223
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs tinyint 224
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs tinyint 238
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs tinyint 239
 TNOU 4 Table with no unique index master..sysresourcelimits master..sysresourcelimits
 TNOU 4 Table with no unique index master..systimeranges master..systimeranges
 MGTP 3 Grant to public master..syslogins  
 MGTP 3 Grant to public master..sysresourcelimits  
 MGTP 3 Grant to public master..systimeranges  
 MGTP 3 Grant to public sybsystemprocs..sp_help_resource_limit  
 MNER 3 No Error Check should check @@error after select into 116
 MNER 3 No Error Check should check return value of exec 165
 MNER 3 No Error Check should check @@error after select into 171
 MNER 3 No Error Check should check return value of exec 249
 MUCO 3 Useless Code Useless Brackets 37
 MUCO 3 Useless Code Useless Brackets 45
 MUCO 3 Useless Code Useless Brackets 53
 MUCO 3 Useless Code Useless Brackets 56
 MUCO 3 Useless Code Useless Brackets 62
 MUCO 3 Useless Code Useless Brackets 71
 MUCO 3 Useless Code Useless Brackets 82
 MUCO 3 Useless Code Useless Brackets 86
 MUCO 3 Useless Code Useless Brackets 89
 MUCO 3 Useless Code Useless Brackets 95
 MUCO 3 Useless Code Useless Brackets 100
 MUCO 3 Useless Code Useless Brackets 103
 MUCO 3 Useless Code Useless Brackets 109
 MUCO 3 Useless Code Useless Brackets 113
 MUCO 3 Useless Code Useless Brackets 254
 QCTC 3 Conditional Table Creation 116
 QCTC 3 Conditional Table Creation 171
 QNAJ 3 Not using ANSI Inner Join 118
 QNAJ 3 Not using ANSI Inner Join 202
 QPNC 3 No column in condition 124
 QPNC 3 No column in condition 125
 QPNC 3 No column in condition 128
 QPNC 3 No column in condition 130
 QPNC 3 No column in condition 132
 QPNC 3 No column in condition 138
 QPNC 3 No column in condition 142
 QPNC 3 No column in condition 153
 QPNC 3 No column in condition 157
 QPNC 3 No column in condition 208
 QPNC 3 No column in condition 209
 QPNC 3 No column in condition 212
 QPNC 3 No column in condition 214
 QPNC 3 No column in condition 216
 QPNC 3 No column in condition 222
 QPNC 3 No column in condition 226
 QPNC 3 No column in condition 237
 QPNC 3 No column in condition 241
 VNRD 3 Variable is not read @procval 72
 VUNU 3 Variable is not used @rangeid 25
 MSUB 2 Subquery Marker 46
 MTR1 2 Metrics: Comments Ratio Comments: 21% 15
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 76 = 80dec - 6exi + 2 15
 MTR3 2 Metrics: Query Complexity Complexity: 129 15
 PRED_QUERY_COLLECTION 2 {r=master..sysresourcelimits, t=master..systimeranges} 0 116
 PRED_QUERY_COLLECTION 2 {r=master..sysresourcelimits, t=master..systimeranges} 0 171

DEPENDENCIES
PROCS AND TABLES USED
reads table master..syslogins (1)  
writes table tempdb..#sphelpreslimit1rs (1) 
writes table tempdb..#sphelpreslimit1rs_v (1) 
calls proc sybsystemprocs..sp_autoformat  
   reads table tempdb..syscolumns (1)  
   calls proc sybsystemprocs..sp_autoformat  
   read_writes table tempdb..#colinfo_af (1) 
   reads table master..syscolumns (1)  
   reads table tempdb..systypes (1)  
   reads table master..systypes (1)  
   calls proc sybsystemprocs..sp_namecrack  
reads table master..sysresourcelimits (1)  
reads table master..systimeranges (1)