DatabaseProcApplicationCreatedLinks
sybsystemprocssp_spaceusage_paramcheck  14 déc. 14Defects Propagation 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 or replace 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=
", 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           */
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   


exec sp_procxmode 'sp_spaceusage_paramcheck', 'AnyMode'
go

Grant Execute on sp_spaceusage_paramcheck to public
go
DEFECTS
 MEST 4 Empty String will be replaced by Single Space 151
 MEST 4 Empty String will be replaced by Single Space 186
 MEST 4 Empty String will be replaced by Single Space 239
 MEST 4 Empty String will be replaced by Single Space 242
 MEST 4 Empty String will be replaced by Single Space 245
 MEST 4 Empty String will be replaced by Single Space 372
 MEST 4 Empty String will be replaced by Single Space 389
 MEST 4 Empty String will be replaced by Single Space 406
 MTYP 4 Assignment type mismatch @str: varchar(512) = varchar(550) 136
 MTYP 4 Assignment type mismatch @str: varchar(512) = varchar(1536) 372
 MTYP 4 Assignment type mismatch @replacedstr: varchar(512) = varchar(1536) 373
 MTYP 4 Assignment type mismatch @str: varchar(512) = varchar(1536) 389
 MTYP 4 Assignment type mismatch @replacedstr: varchar(512) = varchar(1536) 390
 MTYP 4 Assignment type mismatch @str: varchar(512) = varchar(768) 406
 MTYP 4 Assignment type mismatch @replacedstr: varchar(512) = varchar(768) 407
 MGTP 3 Grant to public sybsystemprocs..sp_spaceusage_paramcheck  
 MNER 3 No Error Check should check return value of exec 136
 MNER 3 No Error Check should check return value of exec 151
 MNER 3 No Error Check should check return value of exec 180
 MNER 3 No Error Check should check return value of exec 185
 MNER 3 No Error Check should check return value of exec 215
 MNER 3 No Error Check should check return value of exec 221
 MNER 3 No Error Check should check return value of exec 239
 MNER 3 No Error Check should check return value of exec 242
 MNER 3 No Error Check should check return value of exec 245
 MNER 3 No Error Check should check return value of exec 372
 MNER 3 No Error Check should check return value of exec 389
 MNER 3 No Error Check should check return value of exec 406
 MUCO 3 Useless Code Useless Brackets in create proc 55
 MUCO 3 Useless Code Useless Begin-End Pair 68
 MUCO 3 Useless Code Useless Brackets 131
 MUCO 3 Useless Code Useless Brackets 146
 MUCO 3 Useless Code Useless Brackets 159
 MUCO 3 Useless Code Useless Brackets 199
 MUCO 3 Useless Code Useless Brackets 342
 MUCO 3 Useless Code Useless Brackets 354
 MUCO 3 Useless Code Useless Brackets 366
 MUCO 3 Useless Code Useless Brackets 383
 MUCO 3 Useless Code Useless Brackets 400
 MUCO 3 Useless Code Useless Brackets 417
 VNRD 3 Variable is not read @updallstatcmd 95
 VNRD 3 Variable is not read @retvalue 221
 MTR1 2 Metrics: Comments Ratio Comments: 28% 54
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 60 = 69dec - 11exi + 2 54
 MTR3 2 Metrics: Query Complexity Complexity: 157 54

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_substring_count  
   calls proc sybsystemprocs..sp_split_string  
calls proc sybsystemprocs..sp_replace_string_plus  
   calls proc sybsystemprocs..sp_substring_count  
   calls proc sybsystemprocs..sp_split_string  
calls proc sybsystemprocs..sp_split_string  

CALLERS
called by proc sybsystemprocs..sp_spaceusage