DatabaseProcApplicationCreatedLinks
sybsystemprocssp_helpconfig  14 déc. 14Defects Propagation Dependencies

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


exec sp_procxmode 'sp_helpconfig', 'AnyMode'
go

Grant Execute on sp_helpconfig to public
go
DEFECTS
 MEST 4 Empty String will be replaced by Single Space 144
 MEST 4 Empty String will be replaced by Single Space 146
 MEST 4 Empty String will be replaced by Single Space 174
 MINU 4 Unique Index with nullable columns master..sysconfigures master..sysconfigures
 MINU 4 Unique Index with nullable columns master..sysmessages master..sysmessages
 MTYP 4 Assignment type mismatch @size_str: varchar(30) = varchar(256) 245
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 75
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 104
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 166
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 71
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 72
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 98
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 99
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 129
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 164
 TNOI 4 Table with no index master..syscurconfigs master..syscurconfigs
 MGTP 3 Grant to public master..sysconfigures  
 MGTP 3 Grant to public master..syscurconfigs  
 MGTP 3 Grant to public master..sysmessages  
 MGTP 3 Grant to public sybsystemprocs..sp_helpconfig  
 MLCH 3 Char type with length>30 char(32) 58
 MLCH 3 Char type with length>30 char(32) 59
 MLCH 3 Char type with length>30 char(32) 60
 MLCH 3 Char type with length>30 char(32) 62
 MLCH 3 Char type with length>30 char(32) 63
 MLCH 3 Char type with length>30 char(32) 85
 MLCH 3 Char type with length>30 char(32) 86
 MLCH 3 Char type with length>30 char(32) 87
 MLCH 3 Char type with length>30 char(32) 89
 MLCH 3 Char type with length>30 char(32) 90
 MNER 3 No Error Check should check return value of exec 42
 MNER 3 No Error Check should check @@error after select into 57
 MNER 3 No Error Check should check return value of exec 75
 MNER 3 No Error Check should check @@error after select into 84
 MNER 3 No Error Check should check return value of exec 104
 MNER 3 No Error Check should check @@error after select into 152
 MNER 3 No Error Check should check return value of exec 166
 MNER 3 No Error Check should check return value of exec 203
 MNER 3 No Error Check should check return value of exec 211
 MNER 3 No Error Check should check return value of exec 346
 MNER 3 No Error Check should check return value of exec 361
 MNER 3 No Error Check should check return value of exec 371
 MNER 3 No Error Check should check return value of exec 391
 MNER 3 No Error Check should check return value of exec 401
 MNER 3 No Error Check should check return value of exec 410
 MNER 3 No Error Check should check return value of exec 421
 MUCO 3 Useless Code Useless Brackets in create proc 1
 MUCO 3 Useless Code Useless Brackets 28
 MUCO 3 Useless Code Useless Brackets 50
 MUCO 3 Useless Code Useless Brackets 80
 MUCO 3 Useless Code Useless Brackets 110
 MUCO 3 Useless Code Useless Brackets 137
 MUCO 3 Useless Code Useless Brackets 149
 MUCO 3 Useless Code Useless Brackets 150
 MUCO 3 Useless Code Useless Brackets 177
 MUCO 3 Useless Code Useless Brackets 178
 MUCO 3 Useless Code Useless Brackets 197
 MUCO 3 Useless Code Useless Brackets 205
 MUCO 3 Useless Code Useless Brackets 209
 MUCO 3 Useless Code Useless Brackets 227
 MUCO 3 Useless Code Useless Brackets 237
 MUCO 3 Useless Code Useless Brackets 252
 MUCO 3 Useless Code Useless Brackets 292
 MUCO 3 Useless Code Useless Brackets 310
 MUCO 3 Useless Code Useless Brackets 320
 MUCO 3 Useless Code Useless Brackets 331
 MUCO 3 Useless Code Useless Brackets 339
 MUCO 3 Useless Code Useless Brackets 349
 MUCO 3 Useless Code Useless Brackets 385
 MUCO 3 Useless Code Useless Brackets 394
 MUCO 3 Useless Code Useless Brackets 413
 MUCO 3 Useless Code Useless Brackets 423
 QAFM 3 Var Assignment from potentially many rows 126
 QAFM 3 Var Assignment from potentially many rows 141
 QAFM 3 Var Assignment from potentially many rows 378
 QCTC 3 Conditional Table Creation 57
 QCTC 3 Conditional Table Creation 84
 QDIS 3 Check correct use of 'select distinct' 57
 QDIS 3 Check correct use of 'select distinct' 84
 QGWO 3 Group by/Distinct/Union without order by 57
 QGWO 3 Group by/Distinct/Union without order by 84
 QISO 3 Set isolation level 35
 QNAJ 3 Not using ANSI Inner Join 67
 QNAJ 3 Not using ANSI Inner Join 95
 QNAJ 3 Not using ANSI Inner Join 380
 QNUA 3 Should use Alias: Column name should use alias a 57
 QNUA 3 Should use Alias: Column name should use alias a 84
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error}
142
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered
(name, parent, config)
Intersection: {name}
382
 VUNU 3 Variable is not used @memory_size 9
 VUNU 3 Variable is not used @logical_memory 21
 MTR1 2 Metrics: Comments Ratio Comments: 37% 1
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 27 = 39dec - 14exi + 2 1
 MTR3 2 Metrics: Query Complexity Complexity: 175 1
 PRED_QUERY_COLLECTION 2 {c=master..syscurconfigs, c2=master..sysconfigures} 0 57
 PRED_QUERY_COLLECTION 2 {c=master..sysconfigures, c2=master..syscurconfigs} 0 84
 PRED_QUERY_COLLECTION 2 {c=master..sysconfigures, c2=master..syscurconfigs} 0 378

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_helpconfig_usage  
   calls proc sybsystemprocs..sp_getmessage  
      calls proc sybsystemprocs..sp_validlang  
         reads table master..syslanguages (1)  
      reads table master..sysmessages (1)  
      reads table master..syslanguages (1)  
      reads table sybsystemprocs..sysusermessages  
reads table master..sysconfigures (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) 
   reads table master..syscolumns (1)  
   writes table sybsystemprocs..sp_autoformat_rset_004 
   reads table tempdb..systypes (1)  
   writes table sybsystemprocs..sp_autoformat_rset_001 
   calls proc sybsystemprocs..sp_autoformat  
   reads table master..systypes (1)  
   reads table tempdb..syscolumns (1)  
   writes table sybsystemprocs..sp_autoformat_rset_003 
writes table tempdb..#sphelpconfig2rs (1) 
calls proc sybsystemprocs..sp_helpconfig_estimate  
calls proc sybsystemprocs..sp_validateconfigname  
   writes table sybsystemprocs..sp_validateconfigname_rset_001 
   reads table master..sysconfigures (1)  
   reads table master..syscurconfigs (1)  
   calls proc sybsystemprocs..sp_getmessage  
reads table master..syscurconfigs (1)  
writes table tempdb..#sphelpconfig1rs (1) 
reads table master..sysmessages (1)  
calls proc sybsystemprocs..sp_getmessage  
writes table tempdb..#sphelpconfig3rs (1) 
calls proc sybsystemprocs..sp_aux_getsize  

CALLERS
called by proc sybsystemprocs..sp_helpconfig_gen_estimates_cpi  
   called by proc sybsystemprocs..sp_helpconfig_est_compinfo_poolsize