| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_countmetadata | | 31 Aug 14 | Defects 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 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 82 if @@trancount > 0 83 begin 84 /* 17260, "Can't run %1! from within a transaction." */ 85 raiserror 17260, "sp_countmetadata" 86 return (1) 87 end 88 else 89 begin 90 set chained off 91 end 92 93 set transaction isolation level 1 94 set nocount on 95 96 /* Must have sa_role as this stored procedure is related to config */ 97 if (proc_role("sa_role") < 1) 98 return (1) 99 100 /* 101 ** create temporary table to store the results to be printed. The rid column 102 ** is used to iterate through the table while calculating the memory_required 103 ** for each of the configuration parameters in the table. This is required as 104 ** direct insert or update into a temporary table using value returned by 105 ** config_admin is causing error 3917. Th rid column is 4 digit to accomodate 106 ** current and possibly future additions to the number of configuration 107 ** parameters this sproc can support. 108 */ 109 110 create table #metadata_result_tbl( 111 rid numeric(4, 0) identity, 112 confignum int, 113 metadata_count int NULL, 114 configvalue int NULL, 115 min_value int NULL, 116 max_value int NULL, 117 fullconfigname varchar(255) NULL, 118 memory_required int NULL, 119 dbname varchar(255) NULL) 120 121 /* if @configname = "all" don't validate */ 122 if @configname = "all" 123 begin 124 /* 125 ** fill the result table with required information from syscurconfigs 126 ** and sysconfigures for the config options that this stored procedure 127 ** supports, i.e the list of confignums in 'in' clause below, this list 128 ** should be consistent with the confignums it checks after validation. 129 */ 130 131 insert #metadata_result_tbl(confignum, fullconfigname, configvalue, 132 min_value, max_value, dbname) 133 select b.config, b.name, a.value, 134 minimum_value, maximum_value, 135 case b.config 136 when 105 then 'master' 137 else @dbname 138 end 139 from master.dbo.syscurconfigs a, 140 master.dbo.sysconfigures b 141 where b.config in (105, 107, 263, 408) 142 and a.config = b.config 143 end 144 else 145 begin 146 /* 147 ** Only one configuration parameter is expected. 148 ** Validate the configname and get the corresponding config number, 149 ** and the full name of the config option for printing messages. 150 */ 151 exec @return_value = sp_validateconfigname @configname, 152 @confignum output, 153 @fullconfigname output 154 if @return_value != 0 155 return @return_value 156 157 /* 158 ** This stored procedure supports only a few config parameters. Check 159 ** if the user supplied config parameter is one of them. For now we 160 ** support 'number of open databases', 'number of open objects' and 161 ** 'number of open indexes', 'number of open partitions'. 162 */ 163 if ((@confignum != 105) and (@confignum != 107) and (@confignum != 263) 164 and (@confignum != 408)) 165 begin 166 /* 167 ** 18283, "Configuration parameter '%1!' is not supported in 168 ** this system stored procedure." 169 */ 170 raiserror 18283, @fullconfigname 171 return (1) 172 end 173 insert #metadata_result_tbl(confignum, fullconfigname, configvalue, 174 min_value, max_value, dbname) 175 select b.config, b.name, a.value, 176 minimum_value, maximum_value, 177 case @confignum 178 when 105 then 'master' 179 else @dbname 180 end 181 from master.dbo.syscurconfigs a, 182 master.dbo.sysconfigures b 183 where b.config = @confignum 184 and a.config = b.config 185 186 end 187 188 /* find metadata counts */ 189 190 update #metadata_result_tbl 191 set metadata_count = count_metadata(confignum, dbname) 192 /* 193 ** A meaningful error message would have been printed in the builtin. 194 */ 195 if @@error != 0 196 begin 197 return (1) 198 end 199 200 /* 201 ** find memory required. We need to iterate through the whole table updating 202 ** memory_required column for each row. If we try updating directly into table 203 ** the values returned by config_admin(), it raises error 3917. 204 */ 205 206 select @item_id = min(rid), 207 @max_rid = max(rid) from #metadata_result_tbl 208 209 while @item_id <= @max_rid 210 begin 211 /* set to NULL for validation */ 212 select @confignum = NULL 213 214 select @confignum = confignum, 215 @minimum_value = min_value, 216 @maximum_value = max_value, 217 @metadata_count = metadata_count 218 from #metadata_result_tbl 219 where rid = @item_id 220 221 /* 222 ** check if the rid corresponding to @item_id exists in table. We are 223 ** using identity column which may have gaps in it. 224 **/ 225 if @confignum is not NULL 226 begin 227 select @memory_required = 228 case 229 when @metadata_count < @minimum_value then 230 config_admin(16, @confignum, 231 @minimum_value, 0, NULL, 232 NULL) 233 234 when @metadata_count > @maximum_value then 235 config_admin(16, @confignum, 236 @maximum_value, 0, NULL, 237 NULL) 238 239 else config_admin(16, @confignum, 240 @metadata_count, 0, NULL, 241 NULL) 242 end /* case */ 243 /* 244 ** in case of error built in would have printed error 245 ** message. 246 */ 247 if @memory_required = 0 248 begin 249 return (1) 250 end 251 end 252 update #metadata_result_tbl 253 set memory_required = @memory_required 254 where confignum = @confignum 255 256 select @item_id = @item_id + 1 257 end 258 259 /* print the results */ 260 if @configname = "all" 261 begin 262 select @msg_number = 263 case 264 when @dbname is null then 19452 265 else 19451 266 end 267 exec sp_getmessage @msg_number, @msg output 268 print @msg, @dbname 269 select convert(varchar(40), fullconfigname) "option name", 270 metadata_count "metadata count", 271 configvalue "config value", 272 memory_required "memory required" 273 from #metadata_result_tbl 274 275 return (0) 276 end 277 278 /* if @dbname was not specified, results pertain to all databases */ 279 if @dbname is NULL 280 begin 281 select @dbname = "all" 282 end 283 284 /* print result only for specified config parameter. i.e. @configname != 'all' */ 285 select @metadata_count = metadata_count, 286 @configvalue = configvalue, 287 @memory_required = memory_required 288 from #metadata_result_tbl 289 290 if @confignum = 105 291 begin 292 /* 293 ** 18282, "There are %1! databases, requiring %2! Kbytes of memory. 294 ** The 'open databases' configuration parameter is currently 295 ** set to %3!." 296 */ 297 exec sp_getmessage 18282, @msg output 298 print @msg, @metadata_count, @memory_required, @configvalue 299 end 300 else 301 if @confignum = 107 302 begin 303 /* 304 ** 18280, "There are %1! user objects in %2! database(s), requiring 305 ** %3! Kbytes of memory. The 'open objects' configuration 306 ** parameter is currently set to %4!." 307 */ 308 exec sp_getmessage 18280, @msg output 309 print @msg, @metadata_count, @dbname, @memory_required, @configvalue 310 end 311 else if @confignum = 263 312 begin 313 /* 314 ** 18281, "There are %1! user indexes in %2! database(s), requiring 315 ** %3! Kbytes of memory. The 'open indexes' configuration 316 ** parameter is currently set to %4!." 317 */ 318 exec sp_getmessage 18281, @msg output 319 print @msg, @metadata_count, @dbname, @memory_required, @configvalue 320 end 321 else 322 begin 323 /* 324 ** 19343, "There are %1! user partitions in %2! database(s), requiring 325 ** %3! Kbytes of memory. The 'open partitions' configuration 326 ** parameter is currently set to %4!." 327 */ 328 exec sp_getmessage 19343, @msg output 329 print @msg, @metadata_count, @dbname, @memory_required, @configvalue 330 end 331 332 return (0) 333
exec sp_procxmode 'sp_countmetadata', 'AnyMode' go Grant Execute on sp_countmetadata to public go
| RESULT SETS | |
| sp_countmetadata_rset_001 | |
| DEPENDENCIES |
| PROCS AND TABLES USED calls proc sybsystemprocs..sp_getmessage  reads table master..sysmessages (1)  reads table master..syslanguages (1) reads table sybsystemprocs..sysusermessages calls proc sybsystemprocs..sp_validlang reads table master..syslanguages (1) reads table master..syscurconfigs (1) calls proc sybsystemprocs..sp_validateconfigname reads table master..syscurconfigs (1) calls proc sybsystemprocs..sp_getmessage reads table master..sysconfigures (1) reads table master..sysconfigures (1) read_writes table tempdb..#metadata_result_tbl (1) |
