DatabaseProcApplicationCreatedLinks
sybsystemprocssp_countmetadata  14 déc. 14Defects Propagation Dependencies

1     
2     
3     /*
4     ** Messages for "sp_countmetadata"
5     **
6     ** 17260, "Can't run %1! from within a transaction."
7     **
8     ** 18280, "There are %1! user objects in %2! database(s), requiring %3!
9     **	  Kbytes of memory. The 'open objects' configuration parameter is 
10    **	  currently set to %4!."
11    **
12    ** 18281, "There are %1! user indexes in %2! database(s), requiring %3! 
13    **	  Kbytes of memory. The 'open indexes' configuration parameter is
14    **	  currently set to %4!."
15    **
16    ** 18282, "There are %1! databases, requiring %2! Kbytes of memory. The 
17    **	  'open databases' configuration parameter is currently set to %3!."
18    **
19    ** 18283, "Configuration parameter '%1!' is not supported in this system
20    **	  stored procedure."
21    **
22    ** 19343, "There are %1! user partitions in %2! database(s), requiring %3! 
23    **	  Kbytes of memory. The 'open partitions' configuration parameter is
24    **	  currently set to %4!."
25    **
26    ** 19451, "Metadata counts in database '%1!' are:"
27    **
28    ** 19452, "Metadata counts in all databases are:"
29    */
30    
31    /*
32    ** Syntax:
33    **
34    ** sp_countmetadata configname [, database_name]
35    **
36    ** This stored procedure supports  some of the  configuration parameters
37    ** from the Meta-Data Cache Group of configuration parameters. A config
38    ** parameter in this config group is typically associated with a metadata
39    ** element. For example, "open objects" is associated with sysobjects rows
40    ** as an 'open object' caches sysobjects rows. Hence, it would be good to
41    ** know how many sysobjects  rows are there  in a database or server-wide.
42    **
43    ** This procedure provides the count of a metadata element like sysobjects
44    ** rows in a database or server-wide and provides information on memory
45    ** requirement to cache all of them and the current configuration. For each
46    ** config parameter, we have a minimum value and a maximum value. If the 
47    ** number of metadata elements falls out of the min/max bound, we obtain
48    ** the memory required for the minimum value or maximum value.
49    **
50    ** Parameters: 
51    **	configname    - full/partial name of the configuration parameter.
52    **			"all" may be specified to get counts for all supported
53    **			configuration parameters
54    **	database_name - optional, if supplied, count is from this database.
55    **			else, count is sum of counts from all databases.
56    **			This parameter is ignored if configname parameter
57    **			is 'open databases'. 
58    **
59    ** Returns:
60    **	1 - if error.
61    **	0 - if no error.
62    */
63    
64    create or replace procedure sp_countmetadata
65        @configname varchar(255),
66        @dbname varchar(255) = NULL
67    as
68    
69        declare @configvalue int
70        declare @minimum_value int
71        declare @maximum_value int
72        declare @confignum int
73        declare @return_value int
74        declare @metadata_count int
75        declare @memory_required int
76        declare @fullconfigname varchar(255)
77        declare @msg varchar(1024)
78        declare @max_rid int
79        declare @item_id int
80        declare @msg_number int
81        declare @nullarg char(1)
82        declare @dummy int
83        declare @status int
84        declare @gp_enabled int
85    
86    
87    
88        if @@trancount > 0
89        begin
90            /* 17260, "Can't run %1! from within a transaction." */
91            raiserror 17260, "sp_countmetadata"
92            return (1)
93        end
94        else
95        begin
96            set chained off
97        end
98    
99        set transaction isolation level 1
100       set nocount on
101   
102       /* 
103       ** If granular permissions is not enabled then must have sa_role if 
104       ** granular permissions is enabled then the permission 'manage server' is
105       ** requied.  This stored procedure is related to config. 
106       */
107       select @nullarg = NULL
108       execute @status = sp_aux_checkroleperm "sa_role",
109           "manage server", @nullarg, @gp_enabled output
110   
111       /* For Auditing */
112       if (@gp_enabled = 0)
113       begin
114           if (proc_role("sa_role") = 0)
115               return (1)
116       end
117       else
118       begin
119           select @dummy = proc_auditperm("manage server", @status)
120       end
121   
122       if (@status != 0)
123           return (1)
124   
125       /* 
126       ** create temporary table to store the results to be printed. The rid column
127       ** is used to iterate through the table while calculating the memory_required
128       ** for each of the configuration parameters in the table. This is required as
129       ** direct insert or update into a temporary table using value returned by 
130       ** config_admin is causing error 3917. Th rid column is 4 digit to accomodate
131       ** current and possibly future additions to the number of configuration 
132       ** parameters this sproc can support.
133       */
134   
135       create table #metadata_result_tbl(
136           rid numeric(4, 0) identity,
137           confignum int,
138           metadata_count int NULL,
139           configvalue int NULL,
140           min_value int NULL,
141           max_value int NULL,
142           fullconfigname varchar(255) NULL,
143           memory_required int NULL,
144           dbname varchar(255) NULL)
145   
146       /* if @configname = "all" don't validate */
147       if @configname = "all"
148       begin
149           /*
150           ** fill the result table with required information from syscurconfigs
151           ** and sysconfigures for the config options that this stored procedure
152           ** supports, i.e the list of confignums in 'in' clause below, this list 
153           ** should be consistent with the confignums it checks after validation.
154           */
155   
156           insert #metadata_result_tbl(confignum, fullconfigname, configvalue,
157               min_value, max_value, dbname)
158           select b.config, b.name, a.value,
159               minimum_value, maximum_value,
160               case b.config
161                   when 105 then 'master'
162                   else @dbname
163               end
164           from master.dbo.syscurconfigs a,
165               master.dbo.sysconfigures b
166           where b.config in (105, 107, 263, 408)
167               and a.config = b.config
168       end
169       else
170       begin
171           /*
172           ** Only one configuration parameter is expected.
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,
178               @fullconfigname output
179           if @return_value != 0
180               return @return_value
181   
182           /* 
183           ** This stored procedure supports only a few config parameters. Check 
184           ** if the user supplied config parameter is one of them. For now we
185           ** support 'number of open databases', 'number of open objects' and
186           ** 'number of open indexes', 'number of open partitions'.
187           */
188           if ((@confignum != 105) and (@confignum != 107) and (@confignum != 263)
189                   and (@confignum != 408))
190           begin
191               /*
192               ** 18283, "Configuration parameter '%1!' is not supported in 
193               **	   this system stored procedure."
194               */
195               raiserror 18283, @fullconfigname
196               return (1)
197           end
198           insert #metadata_result_tbl(confignum, fullconfigname, configvalue,
199               min_value, max_value, dbname)
200           select b.config, b.name, a.value,
201               minimum_value, maximum_value,
202               case @confignum
203                   when 105 then 'master'
204                   else @dbname
205               end
206           from master.dbo.syscurconfigs a,
207               master.dbo.sysconfigures b
208           where b.config = @confignum
209               and a.config = b.config
210   
211       end
212   
213       /* find metadata counts */
214   
215       update #metadata_result_tbl
216       set metadata_count = count_metadata(confignum, dbname)
217       /*
218       ** A meaningful error message would have been printed in the builtin.
219       */
220       if @@error != 0
221       begin
222           return (1)
223       end
224   
225       /*
226       ** find memory required. We need to iterate through the whole table updating
227       ** memory_required column for each row. If we try updating directly into table
228       ** the values returned by config_admin(), it raises error 3917.
229       */
230   
231       select @item_id = min(rid),
232           @max_rid = max(rid) from #metadata_result_tbl
233   
234       while @item_id <= @max_rid
235       begin
236           /* set to NULL for validation */
237           select @confignum = NULL
238   
239           select @confignum = confignum,
240               @minimum_value = min_value,
241               @maximum_value = max_value,
242               @metadata_count = metadata_count
243           from #metadata_result_tbl
244           where rid = @item_id
245   
246           /* 
247           ** check if the rid corresponding to @item_id exists in table. We are 
248           ** using identity column which may have gaps in it.
249           **/
250           if @confignum is not NULL
251           begin
252               select @memory_required =
253                   case
254                       when @metadata_count < @minimum_value then
255                       config_admin(16, @confignum,
256                           @minimum_value, 0, NULL,
257                           NULL)
258   
259                       when @metadata_count > @maximum_value then
260                       config_admin(16, @confignum,
261                           @maximum_value, 0, NULL,
262                           NULL)
263   
264                       else config_admin(16, @confignum,
265                               @metadata_count, 0, NULL,
266                               NULL)
267                   end /* case */
268               /* 
269               ** in case of error built in would have printed error 
270               ** message.
271               */
272               if @memory_required = 0
273               begin
274                   return (1)
275               end
276           end
277           update #metadata_result_tbl
278           set memory_required = @memory_required
279           where confignum = @confignum
280   
281           select @item_id = @item_id + 1
282       end
283   
284       /* print the results */
285       if @configname = "all"
286       begin
287           select @msg_number =
288               case
289                   when @dbname is null then 19452
290                   else 19451
291               end
292           exec sp_getmessage @msg_number, @msg output
293           print @msg, @dbname
294           select convert(varchar(40), fullconfigname) "option name",
295               metadata_count "metadata count",
296               configvalue "config value",
297               memory_required "memory required"
298           from #metadata_result_tbl
299   
300           return (0)
301       end
302   
303       /* if @dbname was not specified, results pertain to all databases */
304       if @dbname is NULL
305       begin
306           select @dbname = "all"
307       end
308   
309       /* print result only for specified config parameter. i.e. @configname != 'all' */
310       select @metadata_count = metadata_count,
311           @configvalue = configvalue,
312           @memory_required = memory_required
313       from #metadata_result_tbl
314   
315       if @confignum = 105
316       begin
317           /*
318           ** 18282, "There are %1! databases, requiring %2! Kbytes of memory.
319           **	   The 'open databases' configuration parameter is currently 
320           **	   set to %3!."
321           */
322           exec sp_getmessage 18282, @msg output
323           print @msg, @metadata_count, @memory_required, @configvalue
324       end
325       else
326       if @confignum = 107
327       begin
328           /*
329           ** 18280, "There are %1! user objects in %2! database(s), requiring 
330           **	   %3! Kbytes of memory. The 'open objects' configuration 
331           **	   parameter is currently set to %4!."
332           */
333           exec sp_getmessage 18280, @msg output
334           print @msg, @metadata_count, @dbname, @memory_required, @configvalue
335       end
336       else if @confignum = 263
337       begin
338           /*
339           ** 18281, "There are %1! user indexes in %2! database(s), requiring 
340           **	   %3! Kbytes of memory. The 'open indexes' configuration 
341           **	   parameter is currently set to %4!."
342           */
343           exec sp_getmessage 18281, @msg output
344           print @msg, @metadata_count, @dbname, @memory_required, @configvalue
345       end
346       else
347       begin
348           /*
349           ** 19343, "There are %1! user partitions in %2! database(s), requiring 
350           **	   %3! Kbytes of memory. The 'open partitions' configuration 
351           **	   parameter is currently set to %4!."
352           */
353           exec sp_getmessage 19343, @msg output
354           print @msg, @metadata_count, @dbname, @memory_required, @configvalue
355       end
356   
357       return (0)
358   


exec sp_procxmode 'sp_countmetadata', 'AnyMode'
go

Grant Execute on sp_countmetadata to public
go
RESULT SETS
sp_countmetadata_rset_001

DEFECTS
 MINU 4 Unique Index with nullable columns master..sysconfigures master..sysconfigures
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 208
 QTYP 4 Comparison type mismatch smallint = int 208
 QTYP 4 Comparison type mismatch Comparison type mismatch: numeric(4,0) vs int 244
 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 sybsystemprocs..sp_countmetadata  
 MNER 3 No Error Check should check @@error after insert 156
 MNER 3 No Error Check should check @@error after insert 198
 MNER 3 No Error Check should check @@error after update 277
 MNER 3 No Error Check should check return value of exec 292
 MNER 3 No Error Check should check return value of exec 322
 MNER 3 No Error Check should check return value of exec 333
 MNER 3 No Error Check should check return value of exec 343
 MNER 3 No Error Check should check return value of exec 353
 MUCO 3 Useless Code Useless Brackets 92
 MUCO 3 Useless Code Useless Brackets 112
 MUCO 3 Useless Code Useless Brackets 114
 MUCO 3 Useless Code Useless Brackets 115
 MUCO 3 Useless Code Useless Brackets 122
 MUCO 3 Useless Code Useless Brackets 123
 MUCO 3 Useless Code Useless Brackets 188
 MUCO 3 Useless Code Useless Brackets 196
 MUCO 3 Useless Code Useless Brackets 222
 MUCO 3 Useless Code Useless Brackets 274
 MUCO 3 Useless Code Useless Brackets 300
 MUCO 3 Useless Code Useless Brackets 357
 MUIN 3 Column created using implicit nullability 135
 MUTI 3 Update temptable with identity - 12.5.4 Regression 215
 MUTI 3 Update temptable with identity - 12.5.4 Regression 277
 QAFM 3 Var Assignment from potentially many rows 239
 QAFM 3 Var Assignment from potentially many rows 310
 QCRS 3 Conditional Result Set 294
 QISO 3 Set isolation level 99
 QIWC 3 Insert with not all columns specified missing 3 columns out of 9 156
 QIWC 3 Insert with not all columns specified missing 3 columns out of 9 198
 QNAJ 3 Not using ANSI Inner Join 164
 QNAJ 3 Not using ANSI Inner Join 206
 QNUA 3 Should use Alias: Column maximum_value should use alias a 159
 QNUA 3 Should use Alias: Column minimum_value should use alias a 159
 QNUA 3 Should use Alias: Column maximum_value should use alias a 201
 QNUA 3 Should use Alias: Column minimum_value should use alias a 201
 QSWV 3 Sarg with variable @confignum, Candidate Index: sysconfigures.nc2sysconfigures(config) F 208
 VNRD 3 Variable is not read @dummy 119
 MRST 2 Result Set Marker 294
 MTR1 2 Metrics: Comments Ratio Comments: 52% 64
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 14 = 20dec - 8exi + 2 64
 MTR3 2 Metrics: Query Complexity Complexity: 116 64
 PRED_QUERY_COLLECTION 2 {c=master..sysconfigures, c2=master..syscurconfigs} 0 158
 PRED_QUERY_COLLECTION 2 {c=master..syscurconfigs, c2=master..sysconfigures} 0 200

DATA PROPAGATION detailed
ColumnWritten To
@dbnamesp_countmetadata_rset_001.metadata count °.memory required

DEPENDENCIES
PROCS AND TABLES USED
reads table master..syscurconfigs (1)  
read_writes table tempdb..#metadata_result_tbl (1) 
calls proc sybsystemprocs..sp_validateconfigname  
   writes table sybsystemprocs..sp_validateconfigname_rset_001 
   reads table master..syscurconfigs (1)  
   calls proc sybsystemprocs..sp_getmessage  
      reads table sybsystemprocs..sysusermessages  
      reads table master..sysmessages (1)  
      calls proc sybsystemprocs..sp_validlang  
         reads table master..syslanguages (1)  
      reads table master..syslanguages (1)  
   reads table master..sysconfigures (1)  
calls proc sybsystemprocs..sp_getmessage  
reads table master..sysconfigures (1)  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)  
writes table sybsystemprocs..sp_countmetadata_rset_001