| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_spaceusage_paramcheck | | 31 Aug 14 | Defects Dependencies |
1 2 /* 3 ** Generated by spgenmsgs.pl on Tue Oct 31 23:35:58 2006 4 */ 5 /* 6 ** raiserror Messages for spaceusage_paramcheck [Total 4] 7 ** 8 ** 17993, "'%1!' is not a valid argument." 9 ** 19194, "Argument '%1!' is either invalid or non-unique. Valid arguments are: %2!" 10 ** 19536, "No value for the %1! name given." 11 ** 19537, "Invalid syntax or illegal use of the USING clause." 12 */ 13 /* 14 ** sp_getmessage Messages for spaceusage_paramcheck [Total 0] 15 */ 16 /* 17 ** End spgenmsgs.pl output. 18 */ 19 /* 20 ** SP_SPACEUSAGE_PARAMCHECK 21 ** 22 ** The sub-procedure that does some basic syntactic checks on the 23 ** parameters passed to the sp_spaceusage procedure. Called by 24 ** sp_spaceusage. 25 ** 26 ** Parameters: 27 ** @actionword - The action to be performed. 28 ** @entity_type - Type of entity. Can be "table"/"index". 29 ** @entity_name - The entity name identifier pattern string. 30 ** @using_clause - The USING clause, if any, specified as part of 31 ** the action string in case of "archive", 32 ** "report" and "report summary" action and 33 ** extracted from it in sp_spaceusage. 34 ** @command - A command, if any, that is to be run on the 35 ** object before generating space usage 36 ** estimates for it. 37 ** @from_date - Date, if any, from when on to look for data 38 ** in the archive table to generate report. 39 ** @to_date - Date, if any, till when to look for data in 40 ** the archive table to generate report. 41 ** @select_list - The input SELECT list string 42 ** @where_clause - The input WHERE clause string 43 ** @orderby_clause - The input ORDER BY clause string 44 ** 45 ** Returns 46 ** 0 - if all goes well 47 ** 3 - invalid entity name 48 ** 4 - invalid command 49 ** 5 - invalid USING clause 50 ** 7 - invalid datetime format [from_date/to_date] 51 ** 8 - invalid SELECT list, WHERE clause or ORDER BY clause 52 { 53 */ 54 create procedure sp_spaceusage_paramcheck 55 ( 56 @actionword varchar(20) 57 , @entity_type varchar(12) 58 , @entity_name varchar(550) 59 , @using_clause varchar(350) = NULL 60 , @command varchar(100) = NULL 61 , @from_date varchar(30) = NULL 62 , @to_date varchar(30) = NULL 63 , @select_list varchar(1536) = NULL 64 , @where_clause varchar(1536) = NULL 65 , @orderby_clause varchar(768) = NULL 66 ) 67 as 68 begin -- { -- procedure begins! 69 70 declare @left_substr varchar(350) 71 , @right_substr varchar(350) 72 , @middle_substr varchar(350) 73 , @rest varchar(350) 74 , @compactedcmd varchar(100) 75 , @updstatcmd varchar(20) 76 , @updtabstatcmd varchar(25) 77 , @updindstatcmd varchar(25) 78 , @updallstatcmd varchar(25) 79 , @validcommands varchar(120) 80 , @partcnt int 81 , @commacnt int 82 , @dbnamepattern varchar(10) 83 , @prefixpattern varchar(10) 84 , @unitpattern1 varchar(12) 85 , @unitpattern2 varchar(12) 86 , @retvalue int 87 , @errorfound bit 88 , @wherepattern varchar(15) 89 , @orderbypattern varchar(15) 90 91 select @compactedcmd = NULL 92 , @updstatcmd = "update statistics" 93 , @updtabstatcmd = "update table statistics" 94 , @updindstatcmd = "update index statistics" 95 , @updallstatcmd = "update all statistics" 96 , @errorfound = 0 97 , @dbnamepattern = "dbname=%" 98 , @prefixpattern = "prefix=%" 99 , @unitpattern1 = "unit=[kmg]b" 100 , @unitpattern2 = "unit=pages" 101 , @wherepattern = "where %" 102 , @orderbypattern = "order by %" 103 104 select @validcommands = "'" + @updstatcmd + "'" 105 + ", '" + @updtabstatcmd + "'" 106 + ", '" + @updindstatcmd + "'" 107 108 /* 109 ** Assume action and entity types are checked prior to calling this 110 ** sproc and are valid. Based on it, we do some checks. 111 ** 112 ** Checks include :- 113 ** o entity_name is valid 114 ** . It can not be NULL for 'table'/'index' enity type 115 ** . For 'table' entity type, it has to be 1-part or 2-part. 116 ** . For 'index' entity type, it has to be 2-part or 3-part. 117 ** . For 'tranlog' entity type, it has to be "syslogs" or NULL. 118 ** 119 ** o using_clause is a valid string 120 ** . It is invalid for display mode [and will be ignored, if set]. 121 ** . The string contains "dbname=", or "prefix= 128 if @entity_name is NULL and @entity_type != "tranlog" 129 begin 130 raiserror 19536, @entity_type 131 return (3) 132 end 133 134 select @partcnt = 0 135 136 exec sp_substring_count @entity_name, ".", 1, @partcnt out 137 138 if @partcnt > 3 139 or (@entity_type = "table" and @partcnt not in (0, 1)) 140 or (@entity_type = "index" and @partcnt not in (1, 2)) 141 or (@entity_type = "tranlog" 142 and @entity_name is not NULL 143 and lower(ltrim(rtrim(@entity_name))) != "syslogs") 144 begin 145 raiserror 17993, @entity_name 146 return (3) 147 end 148 149 if @command is not NULL 150 begin 151 exec sp_replace_string_plus @command, "", "", 0 152 , @compactedcmd out 153 154 select @compactedcmd = lower(ltrim(rtrim(@compactedcmd))) 155 156 if @compactedcmd not in (@updstatcmd, @updtabstatcmd, @updindstatcmd) 157 begin 158 raiserror 19194, @command, @validcommands 159 return (4) 160 end 161 end 162 163 -- Use sp_replace_string_plus to remove the white space, if any, in the 164 -- USING clause and then check for case "dbname=%" and the like. This is 165 -- required because, as there's no other way to selectively allow 166 -- any white space in and around "=" and at the same time disallow 167 -- other characters, using the regular expression support in T-SQL. 168 -- 169 if @using_clause is not NULL 170 begin -- { 171 172 select @left_substr = NULL 173 , @right_substr = NULL 174 , @middle_substr = NULL 175 , @rest = NULL 176 , @retvalue = 0 177 , @commacnt = 0 178 , @errorfound = 0 179 180 exec sp_substring_count @using_clause, ",", 1, @commacnt out 181 182 if @commacnt = 0 183 begin -- { -- coma not found 184 185 exec @retvalue = sp_replace_string_plus @using_clause 186 , "", NULL, 0, @using_clause out 187 188 select @using_clause = lower(@using_clause) 189 190 -- For display action only the unit specifier is valid 191 -- as part of the USING clause. 192 -- 193 if @actionword in ("display", "display summary") 194 begin 195 if @using_clause not like @unitpattern1 196 and @using_clause not like @unitpattern2 197 begin 198 raiserror 19537 199 return (5) 200 end 201 end 202 203 if @using_clause not like @dbnamepattern 204 and @using_clause not like @prefixpattern 205 and @using_clause not like @unitpattern1 206 and @using_clause not like @unitpattern2 207 begin 208 select @errorfound = 1 209 end 210 211 end -- } 212 else if @commacnt = 1 or @commacnt = 2 213 begin -- { 214 215 exec @retvalue = sp_split_string @using_clause, ",", 1, 216 @left_substr out, 217 @rest out 218 219 if @commacnt = 2 220 begin 221 exec @retvalue = sp_split_string @rest, ",", 1, 222 @middle_substr out, 223 @right_substr out 224 end 225 else 226 begin 227 select @right_substr = @rest 228 end 229 230 if @left_substr is NULL 231 or @right_substr is NULL 232 or (@middle_substr is NULL and @commacnt = 2) 233 begin 234 select @errorfound = 1 235 end 236 else 237 begin -- { 238 239 exec sp_replace_string_plus @left_substr, "" 240 , NULL, 0, @left_substr out 241 242 exec sp_replace_string_plus @right_substr, "" 243 , NULL, 0, @right_substr out 244 245 exec sp_replace_string_plus @middle_substr, "" 246 , NULL, 0, @middle_substr out 247 248 select @left_substr = lower(@left_substr) 249 , @right_substr = lower(@right_substr) 250 , @middle_substr = lower(@middle_substr) 251 252 if @left_substr like @dbnamepattern 253 begin -- { 254 255 if (@right_substr like @prefixpattern 256 and (@middle_substr like 257 @unitpattern1 258 or @middle_substr like 259 @unitpattern2 260 or @commacnt = 1)) 261 or 262 ((@right_substr like 263 @unitpattern1 264 or @right_substr like 265 @unitpattern2) 266 and (@middle_substr like 267 @prefixpattern 268 or @commacnt = 1)) 269 begin 270 select @errorfound = 0 271 end 272 else 273 begin 274 select @errorfound = 1 275 end 276 277 end -- } 278 else if @left_substr like @prefixpattern 279 begin -- { 280 281 if (@right_substr like @dbnamepattern 282 and (@middle_substr like 283 @unitpattern1 284 or @middle_substr like 285 @unitpattern2 286 or @commacnt = 1)) 287 or 288 ((@right_substr like 289 @unitpattern1 290 or @right_substr like 291 @unitpattern2) 292 and (@middle_substr like 293 @dbnamepattern 294 or @commacnt = 1)) 295 begin 296 select @errorfound = 0 297 end 298 else 299 begin 300 select @errorfound = 1 301 end 302 303 end -- } 304 else if @left_substr like @unitpattern1 305 or @left_substr like @unitpattern2 306 begin -- { 307 308 if (@right_substr like @prefixpattern 309 and (@middle_substr like 310 @dbnamepattern 311 or @commacnt = 1)) 312 or 313 (@right_substr like @dbnamepattern 314 and (@middle_substr like 315 @prefixpattern 316 or @commacnt = 1)) 317 begin 318 select @errorfound = 0 319 end 320 else 321 begin 322 select @errorfound = 1 323 end 324 325 end -- } 326 else 327 begin 328 select @errorfound = 1 329 end 330 331 end -- } 332 333 end -- } 334 else 335 begin 336 select @errorfound = 1 337 end 338 339 if @errorfound = 1 340 begin 341 raiserror 19537 342 return (5) 343 end 344 345 end -- } 346 347 if @from_date is not NULL 348 begin 349 if isdate(@from_date) = 0 350 begin 351 -- invalid from_date 352 select @from_date = "'" + @from_date + "'" 353 raiserror 19123, "'@from_date'", @from_date 354 return (7) 355 end 356 end 357 358 if @to_date is not NULL 359 begin 360 361 if isdate(@to_date) = 0 362 begin 363 -- invalid to_date 364 select @to_date = "'" + @to_date + "'" 365 raiserror 19123, "'@to_date'", @to_date 366 return (7) 367 end 368 end 369 370 if @select_list is not NULL 371 begin 372 exec sp_replace_string_plus @select_list, "", "", 0 373 , @select_list out 374 375 select @select_list = lower(ltrim(rtrim(@select_list))) 376 377 if @select_list LIKE @wherepattern 378 or @select_list LIKE @orderbypattern 379 begin 380 -- invalid SELECT list 381 select @select_list = "'" + @select_list + "'" 382 raiserror 19123, "'@select_list'", @select_list 383 return (8) 384 end 385 end 386 387 if @where_clause is not NULL 388 begin 389 exec sp_replace_string_plus @where_clause, "", "", 0 390 , @where_clause out 391 392 select @where_clause = lower(ltrim(rtrim(@where_clause))) 393 394 if @where_clause NOT LIKE @wherepattern 395 or @where_clause LIKE @orderbypattern 396 begin 397 -- invalid WHERE clause 398 select @where_clause = "'" + @where_clause + "'" 399 raiserror 19123, "'@where_clause'", @where_clause 400 return (8) 401 end 402 end 403 404 if @orderby_clause is not NULL 405 begin 406 exec sp_replace_string_plus @orderby_clause, "", "", 0 407 , @orderby_clause out 408 409 select @orderby_clause = lower(ltrim(rtrim(@orderby_clause))) 410 411 if @orderby_clause NOT LIKE @orderbypattern 412 or @orderby_clause LIKE @wherepattern 413 begin 414 -- invalid ORDER BY clause 415 select @orderby_clause = "'" + @orderby_clause + "'" 416 raiserror 19123, "'@orderby_clause'", @orderby_clause 417 return (8) 418 end 419 end 420 421 end -- } -- } 422", or both. 122 ** 123 ** o command is a valid allowed command 124 ** . Currently valid commands are 'update statistics', 'update table 125 ** statistics', 'update all statistics'. 126 ** 127 */
exec sp_procxmode 'sp_spaceusage_paramcheck', 'AnyMode' go Grant Execute on sp_spaceusage_paramcheck to public go
| DEPENDENCIES |
| PROCS AND TABLES USED calls proc sybsystemprocs..sp_replace_string_plus calls proc sybsystemprocs..sp_split_string calls proc sybsystemprocs..sp_substring_count calls proc sybsystemprocs..sp_split_string calls proc sybsystemprocs..sp_substring_count calls proc sybsystemprocs..sp_split_string CALLERS called by proc sybsystemprocs..sp_spaceusage  |
