1
2
3 /*
4 ** Messages for "sp_helpconfig"
5 **
6 ** 17260, "Can't run %1! from within a transaction."
7 **
8 ** 18283, "Configuration parameter '%1!' is not supported in this
9 ** system stored procedure, or the usage is incorrect. Please refer
10 ** to System Administration Guide for help."
11 **
12 ** 18395, "Configuration parameter, '%1!', will consume %2!K of memory if
13 ** configured at %3!.
14 **
15 ** 18396, "Configuration parameter, '%1!', can be configured to %2! to
16 ** fit in %3! of memory.
17 **
18 ** 18397, "Changing the value of '%1!' does not increase the amount of memory
19 ** SQL Server uses.
20 **
21 ** 18401, "Configuration parameter, 'max memory', will need to be set to
22 ** %1! (%2!K) in order for 'total data cache size' to have a value of
23 ** %3!K.
24 **
25 ** 18403, "Configuration parameter, 'max memory', will need to be set to
26 ** %1! (%2!K) in order for the procedure cache to have a value of %3!.
27 **
28 ** 18521, "Omni services must be enabled and loaded in order to perform
29 ** memory calculations."
30 **
31 ** 18916, "Changing the value of '%1!' to '%2!' increases the amount of
32 ** memory ASE uses by %3! K.
33 **
34 ** 18917, "Changing the value of '%1!' to '%2!' reduces the amount of
35 ** memory ASE uses by %3! K. The reduced memory could be reused
36 ** when this configure value changes, but will not be released
37 ** until ASE restarts."
38 **
39 */
40
41 /*
42 ** Syntax:
43 **
44 ** sp_helpconfig "configname", "size"
45 **
46 ** This stored procedure provides help information for a config parameter.
47 ** The help information provided is specific to a config parameter.
48 **
49 ** Parameters:
50 ** configname - full/partial name of the config parameter.
51 ** special: if configname is "static options",
52 ** "dynamic options" or "read-only options" we will
53 ** list all config parameters of that type.
54 ** size - for parameters which use memory, this is the size
55 ** of either the proposed config value, or the size
56 ** of memory. If either, K, M, or G are used, it is
57 ** interpreted that size is a memory value and
58 ** a message will be printed that explains how many
59 ** of configname will fit in size.
60 ** if only a number is used, then a message will be
61 ** printed that will explain how much memory will be
62 ** consumed by size number of confignames.
63 **
64 ** Returns:
65 ** 1 - if error.
66 ** 0 - if no error.
67 */
68
69
70 create procedure sp_helpconfig
71 @configname varchar(80),
72 @size varchar(30) = NULL
73 as
74
75 declare @confignum int
76 declare @return_value int
77 declare @message_num int
78 declare @memory_size int
79 declare @status int
80 declare @datatype int
81 declare @value int
82 declare @dir int
83 declare @pages int
84 declare @msg varchar(1024)
85 declare @fullconfigname varchar(80)
86 declare @size_len int
87 declare @count int
88 declare @found_memory int
89 declare @unit_size char(1)
90 declare @logical_memory int /* current total logical memory */
91 declare @additional_memory int /* increase in logical memory due to change*/
92
93 if @@trancount > 0
94 begin
95 /* 17260, "Can't run %1! from within a transaction." */
96 raiserror 17260, "sp_helpconfig"
97 return (1)
98 end
99 else
100 begin
101 set chained off
102 end
103
104 set transaction isolation level 1
105
106 /* we don't want too much of output */
107 set nocount on
108
109 /*
110 ** Check if configname is one of 'static options', 'dynamic options',
111 ** 'read-only options' or 'cluster options'
112 */
113 if (@configname in ('static options', 'dynamic options', 'read-only options', 'cluster options'))
114 begin
115 if @@system_view = 'INSTANCE'
116 begin
117 select distinct Config_Name = name,
118 Config_Value = convert(char(32), space(11 - char_length(
119 isnull(a.value2, convert(char(32), a.value)))) +
120 isnull(a.value2, convert(char(32), a.value))),
121 Run_Value = convert(char(11), space(11 - char_length(
122 isnull(b.value2, convert(char(32), b.value)))) +
123 isnull(b.value2, convert(char(32), b.value))),
124 Unit = b.unit
125
126 into #sphelpconfig1rs
127 from master.dbo.sysconfigures a, master.dbo.syscurconfigs b
128 where
129 a.config = b.config
130
131 and a.parent != 19
132 and a.config != 19
133 and b.type like "%" + substring(@configname, 1, char_length(@configname) - 8) + "%"
134
135 exec sp_autoformat @fulltabname = #sphelpconfig1rs,
136 @selectlist = "'Config Name'=Config_Name,'Config Value'=Config_Value,'Run Value'=Run_Value,'Unit'=Unit,'Instance Name'=Instance_Name",
137 @orderby = "order by lower(Config_Name)"
138 drop table #sphelpconfig1rs
139 return (0)
140 end
141 else
142 begin
143 select distinct Config_Name = name,
144 Config_Value = convert(char(32), space(11 - char_length(
145 isnull(a.value2, convert(char(32), a.value)))) +
146 isnull(a.value2, convert(char(32), a.value))),
147 Run_Value = convert(char(11), space(11 - char_length(
148 isnull(b.value2, convert(char(32), b.value)))) +
149 isnull(b.value2, convert(char(32), b.value))),
150 Unit = b.unit
151
152
153 into #sphelpconfig3rs
154 from master.dbo.sysconfigures a, master.dbo.syscurconfigs b
155 where
156 a.config = b.config
157 and a.parent != 19
158 and a.config != 19
159
160 and b.type like "%" + substring(@configname, 1, char_length(@configname) - 8) + "%"
161
162
163 exec sp_autoformat @fulltabname = #sphelpconfig3rs,
164 @selectlist = "'Config Name'=Config_Name,'Config Value'=Config_Value,'Run Value'=Run_Value,'Unit'=Unit",
165 @orderby = "order by lower(Config_Name)"
166
167 drop table #sphelpconfig3rs
168 return (0)
169 end
170 end
171
172 /*
173 ** Validate the configname and get the corresponding config number,
174 ** and the full name of the config option for printing messages.
175 */
176 exec @return_value = sp_validateconfigname @configname,
177 @confignum output, @fullconfigname output
178 if @return_value != 0
179 return @return_value
180
181 /*
182 ** Retrieve some info from syscurconfigs
183 */
184 select @message_num = message_num,
185 @status = status,
186 @datatype = datatype
187 from master..syscurconfigs where config = @confignum
188
189 /* Localization? */
190 select @msg = description from master.dbo.sysmessages
191 where error = @message_num
192
193 print ""
194 print @msg
195 print ""
196
197 /* If the config option is a string valued option, return */
198 if (@datatype = 5)
199 return (0)
200
201 select Minimum_Value = minimum_value,
202 Maximum_Value = maximum_value,
203 Default_Value = convert(int, defvalue),
204 Current_Value = value,
205 Memory_Used = convert(char(11), space(11 - char_length(
206 convert(varchar(11), comment))) +
207 convert(varchar(11), comment)),
208 Unit = unit,
209
210 Type = type
211
212 into #sphelpconfig2rs
213 from master..syscurconfigs where config = @confignum
214 exec sp_autoformat @fulltabname = #sphelpconfig2rs,
215
216 @selectlist = "'Minimum Value'=Minimum_Value, 'Maximum Value'=Maximum_Value, 'Default Value'=Default_Value,'Current Value'=Current_Value,'Memory Used'=Memory_Used,'Unit'=Unit,'Type'=Type"
217
218 drop table #sphelpconfig2rs
219 print ""
220
221 /* Done if the second parameter was not passed into the sproc */
222 if (@size is NULL)
223 return (0)
224
225 /*
226 ** If this config parameter uses memory and a second param was passed in
227 ** then additional processing is needed.
228 **
229 ** If the second param is of type '%d [k | K | m | M]', then user supplied
230 ** a memory value, and is asking for how many of a particular parameter
231 ** will fit in that memory value.
232 **
233 ** If the second param is of type '%d', then user supplied a config value
234 ** and is asking for how much memory value will use.
235 **
236 */
237
238 /* Chech to see if the parameter uses memory by looking for the correct bit */
239 select @status = @status & 64
240
241 if (@status != 64)
242 begin
243 /*
244 ** 18397, Changing the value of '%1!' does not increase the amount
245 ** of memory SQL Server uses.
246 */
247 exec sp_getmessage 18397, @msg output
248 print @msg, @fullconfigname
249 return (0)
250 end
251
252 /*
253 ** Check if the second parameter contains non integer character(s).
254 */
255 select @size_len = char_length(@size)
256 select @count = 1
257 select @found_memory = 0
258 while (@count <= @size_len) and
259 (@found_memory = 0)
260 begin
261 select @unit_size = substring(@size, @count, 1)
262 if (@unit_size not like "[0-9]")
263 begin
264 if (@count != 1) or (@unit_size != "-")
265 begin
266 select @found_memory = 1
267 end
268 end
269 select @count = @count + 1
270 end
271
272 if (@found_memory = 1)
273 begin
274 /*
275 ** second parameter is a memory size.
276 **
277 ** sp_aux_getsize returns the memory size in terms of KB.
278 ** Note that negitive size will be caught by sp_aux_getsize
279 */
280 exec @return_value = sp_aux_getsize @size, @value output
281
282 /*
283 ** check if input had an error, if so return. Message was
284 ** printed in sp_aux_getsize.
285 */
286 if @return_value = 0
287 return (1)
288
289 select @dir = 1
290 end
291 else
292 begin
293 /*
294 ** second parameter is a value
295 */
296 select @value = convert(int, @size)
297
298 select @dir = 0
299 end
300
301 /*
302 ** Validate the directions ( 0 or 1 ) based on applicability to
303 ** a configuration parameter.
304 **
305 ** For 'total datacache size (132) we will estimate the total logical memory
306 ** given X mount of datacache in unit sizes (K, M etc). ( dir = 1)
307 **
308 ** For procedure cache size (146) we will estimate the total logical memory
309 ** given X number of procedure cache size in pages. ( dir = 0)
310 **
311 ** Given a stacksize, we will compute the memory consumed. But the stack size
312 ** can be given in terms of K ( assumed )
313 **
314 */
315 if @dir = 0
316 begin
317 if @confignum in (132, 104)
318 begin
319 /*
320 ** 18283, "Configuration parameter '%1!' is not supported in
321 ** this system stored procedure, or the usage is
322 ** incorrect. Please refer to System Administration
323 ** Guide for help"
324 */
325 raiserror 18283, @fullconfigname
326 return (1)
327 end
328 end
329
330 if @dir = 1
331 begin
332 if @confignum in (146, 134, 212, 104)
333 begin
334 /*
335 ** 18283, "Configuration parameter '%1!' is not supported in
336 ** this system stored procedure, or the usage is
337 ** incorrect. Please refer to System Administration
338 ** Guide for help"
339 */
340 raiserror 18283, @fullconfigname
341 return (1)
342 end
343 end
344
345 select @return_value = config_admin(16, @confignum, @value, @dir,
346 NULL, NULL)
347
348 /*
349 ** Return if error occurred in config_admin.
350 */
351 if (@return_value = 0)
352 begin
353 if @confignum = 278
354 begin
355 /*
356 ** 18521, "Omni services must be enabled and loaded
357 ** in order to perform memory calculations."
358 */
359 raiserror 18521
360 end
361
362 return (1)
363 end
364
365 /*
366 ** special messages for the following configuration parameters:
367 ** 'total data cache size' (config number 132)
368 ** 'procedure cache size' (config number 146)
369 */
370 if (@confignum = 132 and @dir = 1)
371 begin
372 /*
373 ** 18401, Configuration parameter, 'max memory', will need to
374 ** be set to %1! (%2!K) in order for 'total data cache size'
375 ** to have a value of %3!.
376 */
377 exec sp_getmessage 18401, @msg output
378 select @pages = @return_value / 2
379 print @msg, @pages, @return_value, @size
380 return (0)
381 end
382
383 if @dir = 0
384 begin
385 if @confignum = 146
386 begin
387 /*
388 ** 18403, Configuration parameter, 'max memory', will
389 ** need to be set to %1! (%2!K) in order for the procedure
390 ** cache to have a value of %3!.
391 */
392 exec sp_getmessage 18403, @msg output
393 select @pages = @return_value / 2
394 print @msg, @pages, @return_value, @size
395 end
396 else
397 begin
398 /*
399 ** 18395, Configuration parameter, '%1!', will consume %2!K
400 ** of memory if configured at %3!.
401 */
402 exec sp_getmessage 18395, @msg output
403 print @msg, @fullconfigname, @return_value, @size
404 end
405
406 /*
407 ** Calculate increase in 'total logical memory'
408 */
409 select @additional_memory = (config_admin(21, @confignum, @value,
410 0, NULL, NULL) - b.value) * 2
411 from master.dbo.sysconfigures a,
412 master.dbo.syscurconfigs b
413 where a.name = 'total logical memory'
414 and a.config = b.config
415
416 if (@additional_memory > 0)
417 begin
418 /*
419 ** 18916, Changing the value of '%1!' to '%2!' increases
420 ** the amount of memory ASE uses by %3! K.
421 */
422 exec sp_getmessage 18916, @msg output
423 print @msg, @fullconfigname, @size, @additional_memory
424 end
425 else if (@additional_memory < 0)
426 begin
427 /*
428 ** 18917, Changing the value of '%1!' to '%2!' reduces
429 ** the amount of memory ASE uses by %3! K.
430 */
431 select @additional_memory = @additional_memory * - 1
432 exec sp_getmessage 18917, @msg output
433 print @msg, @fullconfigname, @size, @additional_memory
434 end
435 else
436 begin
437 /*
438 ** 18397, Changing the value of '%1!' does not increase
439 ** the amount of memory SQL Server uses.
440 */
441 exec sp_getmessage 18397, @msg output
442 print @msg, @fullconfigname
443 end
444 return (0)
445 end
446 else
447 begin
448 /*
449 ** 18396, Configuration parameter, '%1!', can be configured
450 ** to %2! to fit in %3! of memory.
451 */
452 exec sp_getmessage 18396, @msg output
453 print @msg, @fullconfigname, @return_value, @size
454 return (0)
455 end
456
457
exec sp_procxmode 'sp_helpconfig', 'AnyMode'
go
Grant Execute on sp_helpconfig to public
go
