DatabaseProcApplicationCreatedLinks
sybsystemprocssp_spaceusage_showhelp_report  14 déc. 14Defects Propagation Dependencies

1     
2     /*
3     **	SP_SPACEUSAGE_SHOWHELP_REPORT
4     **
5     **	The sub-procedure to print "report" action specific help/usage 
6     **	information for the sp_spaceusage procedure. Called by sp_spaceusage.
7     **
8     **	Parameters
9     **		@helpentity	- Entity type on which help is sought.
10    **		@verbose	- Information mode is verbose or terse.
11    **
12    **	Returns 
13    **		0 - always. Prints the usage information or error messge for
14    **		    invalid action/entity.
15    {
16    */
17    create or replace procedure sp_spaceusage_showhelp_report
18    (
19        @helpentity varchar(12)
20        , @verbose bit
21    )
22    as
23        begin -- {
24    
25            declare @pattern varchar(50)
26                , @pattern4table varchar(50)
27                , @pattern4index varchar(50)
28                , @msg varchar(256)
29    
30            select @helpentity = lower(ltrim(rtrim(@helpentity)))
31                , @pattern4index = "nc%"
32                , @pattern4table = "my%"
33    
34            if @helpentity is NULL
35                or @helpentity not in ("index", "table", "tranlog")
36                or @verbose = 1
37            begin -- {
38    
39                exec sp_getmessage 19590, @msg output
40                print @msg
41                print ""
42    
43                exec sp_getmessage 18954, @msg output
44                print @msg
45    
46                print "sp_spaceusage 'report', {'table'|'index'}, <name>"
47                print "		[,<select_list> [,<where_clause> [,<order_by> [,<from_date> [,<to_date>]]]]]"
48                print ""
49                print "sp_spaceusage 'report summary', {'table'|'index'}, <name>"
50                print "		[,<where_clause> [,<order_by> [,<from_date> [,<to_date>]]]]"
51                print ""
52                print "sp_spaceusage 'report', 'tranlog' [,{'syslogs'|NULL}"
53                print "		[,<select_list> [,<where_clause> [,<order_by> [,<from_date> [,<to_date>]]]]]]"
54                print ""
55    
56                exec sp_getmessage 19539, @msg out
57                print @msg
58    
59                print "sp_spaceusage 'help', 'report', 'table'"
60                print "sp_spaceusage 'help', 'report', 'index'"
61                print "sp_spaceusage 'help', 'report', 'tranlog'"
62                print ""
63                print ""
64    
65            end -- }
66    
67            select @pattern = case @helpentity
68                    when "index"
69                    then @pattern4table + "." + @pattern4index
70                    else @pattern4table
71                end
72    
73            if @helpentity in ("table", "index")
74            begin -- {
75    
76                exec sp_getmessage 19540, @msg out
77                print @msg, @helpentity
78    
79                exec sp_getmessage 18954, @msg output
80                print @msg
81    
82                print "sp_spaceusage 'report [using {<using_item>[,<using_item>]...}]', '%1!', <name>"
83                    , @helpentity
84                print "		[,<select_list> [,<where_clause> [,<order_by> [,<from_date> [,<to_date>]]]]]"
85                print ""
86                print "sp_spaceusage 'report summary [using {<using_item>[,<using_item>]...}]', '%1!', <name>"
87                    , @helpentity
88                print "		[,<where_clause> [,<order_by> [,<from_date> [,<to_date>]]]]"
89                print ""
90    
91                print "%1!<using_item> :: { unit={KB|MB|GB|PAGES} | dbname=<db> | prefix=<string> }"
92                    , "	"
93                print ""
94    
95                exec sp_getmessage 19541, @msg out
96                print @msg
97                print ""
98    
99                if @helpentity = "table"
100               begin
101                   exec sp_getmessage 19551, @msg out
102               end
103               else
104               begin
105                   exec sp_getmessage 19552, @msg out
106               end
107               print @msg, "  -- ", NULL, "MB", "nc"
108   
109               exec sp_getmessage 19546, @msg out
110               print @msg, "  -- ", "my", "'user1'"
111   
112               exec sp_getmessage 19553, @msg out
113               print @msg, "  -- ", "spaceusage_object"
114   
115               print "  -- "
116               print "  sp_spaceusage 'report using unit=MB', '%1!', 'user1.%2!'"
117                   , @helpentity, @pattern
118   
119               print ""
120   
121               if @helpentity = "table"
122               begin
123                   exec sp_getmessage 19551, @msg out
124               end
125               else
126               begin
127                   exec sp_getmessage 19552, @msg out
128               end
129               print @msg, "  -- ", NULL, "KB", "nc"
130   
131               exec sp_getmessage 19546, @msg out
132               print @msg, "  -- ", "my", "'user1'"
133   
134               exec sp_getmessage 19554, @msg out
135               print @msg, "  -- ", "spaceusage_object", "mydb"
136   
137               exec sp_getmessage 19555, @msg out
138               print @msg, "  -- ", "Aug 30 2005  6:21AM", "'today'"
139   
140               print "  -- "
141               print "  sp_spaceusage 'report using dbname=mydb', '%1!', 'user1.%2!', NULL, NULL,"
142                   , @helpentity, @pattern
143               print "		 NULL, 'Aug 30 2005  6:21AM'"
144   
145               print ""
146   
147               if @helpentity = "table"
148               begin
149                   exec sp_getmessage 19551, @msg out
150               end
151               else
152               begin
153                   exec sp_getmessage 19552, @msg out
154               end
155               print @msg, "  -- ", "ALL ", "KB", "nc"
156   
157               exec sp_getmessage 19546, @msg out
158               print @msg, "  -- ", "my", "'dbo'"
159   
160               exec sp_getmessage 19553, @msg out
161               print @msg, "  -- ", "spaceusage_object"
162   
163               exec sp_getmessage 19556, @msg out
164               print @msg, "  -- ", "'Aug 30 2005  6:21AM'"
165   
166               exec sp_getmessage 19549, @msg out
167               print @msg, "  -- ", "PctBloatRsvdPages > 40", ", "
168                   , "PctBloatRsvdPages"
169   
170               print "  -- "
171               print "  sp_spaceusage 'report', '%1!', 'dbo.%2!', '*',"
172                   , @helpentity, @pattern
173               print "		'where PctBloatRsvdPages > 40','order by PctBloatRsvdPages desc',"
174               print "		 NULL, 'Aug 30 2005  6:21AM'"
175   
176               print ""
177   
178               if @helpentity = "table"
179               begin
180                   exec sp_getmessage 19551, @msg out
181               end
182               else
183               begin
184                   exec sp_getmessage 19552, @msg out
185               end
186               print @msg, "  -- ", "'Id, IndId, PtnId, PctBloatRsvdPages' "
187                   , "KB", "nc"
188   
189               exec sp_getmessage 19550, @msg out
190               print @msg, "  -- ", "my"
191   
192               exec sp_getmessage 19553, @msg out
193               print @msg, "  -- ", "weekly_spaceusage_object"
194   
195               exec sp_getmessage 19555, @msg out
196               print @msg, "  -- ", "Aug 30 2005 6:21AM"
197                   , "'Aug 30 2006 6:21AM'"
198   
199               exec sp_getmessage 19548, @msg out
200               print @msg, "  -- ", "PctBloatRsvdPages > 40"
201   
202               select @pattern = "%." + @pattern
203   
204               print "  -- "
205               print "  sp_spaceusage 'report using prefix=weekly_', '%1!', '%2!',"
206                   , @helpentity, @pattern
207               print "		 'Id,IndId,PtnId,PctBloatRsvdPages', 'where PctBloatRsvdPages>40',"
208               print "		 NULL, 'Aug 30 2005 6:21AM', 'Aug 30 2006  6:21AM'"
209   
210               print ""
211   
212               select @pattern = case @helpentity
213                       when "index"
214                       then "mytable.%"
215                       else "mytable"
216                   end
217   
218               if @helpentity = "table"
219               begin
220                   exec sp_getmessage 19557, @msg out
221               end
222               else
223               begin
224                   exec sp_getmessage 19558, @msg out
225               end
226               print @msg, "  -- ", "KB", "mytable"
227   
228               exec sp_getmessage 19554, @msg out
229               print @msg, "  -- ", "weekly_spaceusage_object", "mydb"
230   
231               exec sp_getmessage 19548, @msg out
232               print @msg, "  -- ", "PctBloatRsvdPages > 40"
233   
234               print "  -- "
235               print "  sp_spaceusage 'report summary using dbname=mydb,prefix=weekly_', '%1!', '%2!',"
236                   , @helpentity, @pattern
237               print "		 'where PctBloatRsvdPages > 40'"
238   
239               print ""
240   
241           end -- }
242           if @helpentity = "tranlog"
243           begin -- {
244   
245               exec sp_getmessage 19540, @msg out
246               print @msg, @helpentity
247   
248               exec sp_getmessage 18954, @msg output
249               print @msg
250   
251               print "sp_spaceusage 'report [using {<using_item>[,<using_item>]...}]', 'tranlog' [,{'syslogs'|NULL}"
252               print "		[,<select_list> [,<where_clause> [,<order_by> [,<from_date> [,<to_date>]]]]]]"
253               print ""
254   
255               print "%1!<using_item> :: { unit={KB|MB|GB|PAGES} | dbname=<db> | prefix=<string> }"
256                   , "	"
257               print ""
258   
259               exec sp_getmessage 19541, @msg out
260               print @msg
261               print ""
262   
263               exec sp_getmessage 19551, @msg out
264               select @msg = @msg + " transaction log"
265               print @msg, "  -- ", NULL, "MB"
266   
267               exec sp_getmessage 19553, @msg out
268               print @msg, "  -- ", "spaceusage_tranlog"
269   
270               print "  -- "
271               print "  sp_spaceusage 'report using unit=MB', 'tranlog', 'syslogs'"
272               print ""
273   
274               exec sp_getmessage 19551, @msg out
275               select @msg = @msg + " transaction log"
276               print @msg, "  -- ", NULL, "KB"
277   
278               exec sp_getmessage 19554, @msg out
279               print @msg, "  -- ", "spaceusage_tranlog", "mydb"
280   
281               exec sp_getmessage 19555, @msg out
282               print @msg, "  -- ", "Aug 30 2005  6:21AM", "'today'"
283   
284               print "  -- "
285               print "  sp_spaceusage 'report using dbname=mydb', 'tranlog', NULL, NULL,"
286               print "		 NULL, 'Aug 30 2005  6:21AM'"
287   
288               print ""
289   
290               exec sp_getmessage 19551, @msg out
291               select @msg = @msg + " transaction log"
292               print @msg, "  -- ", "ALL ", "KB", "nc"
293   
294               exec sp_getmessage 19553, @msg out
295               print @msg, "  -- ", "spaceusage_tranlog"
296   
297               exec sp_getmessage 19556, @msg out
298               print @msg, "  -- ", "'Aug 30 2005  6:21AM'"
299   
300               exec sp_getmessage 19549, @msg out
301               print @msg, "  -- ", "IsLogFull = 1", ", "
302                   , "TotalPages"
303   
304               print "  -- "
305               print "  sp_spaceusage 'report', 'tranlog', NULL, '*',"
306               print "		'where IsLogFull = 1','order by TotalPages desc',"
307               print "		 NULL, 'Aug 30 2005  6:21AM'"
308   
309               print ""
310   
311               exec sp_getmessage 19551, @msg out
312               select @msg = @msg + " transaction log"
313               print @msg, "  -- ", "'DBName, Id, TotalPages, UsedPages, FreePages' "
314                   , "MB"
315   
316               exec sp_getmessage 19553, @msg out
317               print @msg, "  -- ", "weekly_spaceusage_tranlog"
318   
319               exec sp_getmessage 19555, @msg out
320               print @msg, "  -- ", "Aug 30 2005 6:21AM"
321                   , "'Aug 30 2006 6:21AM'"
322   
323               exec sp_getmessage 19548, @msg out
324               print @msg, "  -- ", "TotalPages > 2"
325   
326               print "  -- "
327               print "  sp_spaceusage 'report using unit=MB,prefix=weekly_', 'tranlog', NULL,"
328               print "		 'DBName,Id,TotalPages,UsedPages,FreePages', 'where TotalPages>2',"
329               print "		 NULL, 'Aug 30 2005 6:21AM', 'Aug 30 2006  6:21AM'"
330   
331               print ""
332   
333           end -- }
334   
335           return (0)
336   
337       end -- }	-- }
338   


exec sp_procxmode 'sp_spaceusage_showhelp_report', 'AnyMode'
go

Grant Execute on sp_spaceusage_showhelp_report to public
go
DEFECTS
 MEST 4 Empty String will be replaced by Single Space 41
 MEST 4 Empty String will be replaced by Single Space 48
 MEST 4 Empty String will be replaced by Single Space 51
 MEST 4 Empty String will be replaced by Single Space 54
 MEST 4 Empty String will be replaced by Single Space 62
 MEST 4 Empty String will be replaced by Single Space 63
 MEST 4 Empty String will be replaced by Single Space 85
 MEST 4 Empty String will be replaced by Single Space 89
 MEST 4 Empty String will be replaced by Single Space 93
 MEST 4 Empty String will be replaced by Single Space 97
 MEST 4 Empty String will be replaced by Single Space 119
 MEST 4 Empty String will be replaced by Single Space 145
 MEST 4 Empty String will be replaced by Single Space 176
 MEST 4 Empty String will be replaced by Single Space 210
 MEST 4 Empty String will be replaced by Single Space 239
 MEST 4 Empty String will be replaced by Single Space 253
 MEST 4 Empty String will be replaced by Single Space 257
 MEST 4 Empty String will be replaced by Single Space 261
 MEST 4 Empty String will be replaced by Single Space 272
 MEST 4 Empty String will be replaced by Single Space 288
 MEST 4 Empty String will be replaced by Single Space 309
 MEST 4 Empty String will be replaced by Single Space 331
 MGTP 3 Grant to public sybsystemprocs..sp_spaceusage_showhelp_report  
 MNER 3 No Error Check should check return value of exec 39
 MNER 3 No Error Check should check return value of exec 43
 MNER 3 No Error Check should check return value of exec 56
 MNER 3 No Error Check should check return value of exec 76
 MNER 3 No Error Check should check return value of exec 79
 MNER 3 No Error Check should check return value of exec 95
 MNER 3 No Error Check should check return value of exec 101
 MNER 3 No Error Check should check return value of exec 105
 MNER 3 No Error Check should check return value of exec 109
 MNER 3 No Error Check should check return value of exec 112
 MNER 3 No Error Check should check return value of exec 123
 MNER 3 No Error Check should check return value of exec 127
 MNER 3 No Error Check should check return value of exec 131
 MNER 3 No Error Check should check return value of exec 134
 MNER 3 No Error Check should check return value of exec 137
 MNER 3 No Error Check should check return value of exec 149
 MNER 3 No Error Check should check return value of exec 153
 MNER 3 No Error Check should check return value of exec 157
 MNER 3 No Error Check should check return value of exec 160
 MNER 3 No Error Check should check return value of exec 163
 MNER 3 No Error Check should check return value of exec 166
 MNER 3 No Error Check should check return value of exec 180
 MNER 3 No Error Check should check return value of exec 184
 MNER 3 No Error Check should check return value of exec 189
 MNER 3 No Error Check should check return value of exec 192
 MNER 3 No Error Check should check return value of exec 195
 MNER 3 No Error Check should check return value of exec 199
 MNER 3 No Error Check should check return value of exec 220
 MNER 3 No Error Check should check return value of exec 224
 MNER 3 No Error Check should check return value of exec 228
 MNER 3 No Error Check should check return value of exec 231
 MNER 3 No Error Check should check return value of exec 245
 MNER 3 No Error Check should check return value of exec 248
 MNER 3 No Error Check should check return value of exec 259
 MNER 3 No Error Check should check return value of exec 263
 MNER 3 No Error Check should check return value of exec 267
 MNER 3 No Error Check should check return value of exec 274
 MNER 3 No Error Check should check return value of exec 278
 MNER 3 No Error Check should check return value of exec 281
 MNER 3 No Error Check should check return value of exec 290
 MNER 3 No Error Check should check return value of exec 294
 MNER 3 No Error Check should check return value of exec 297
 MNER 3 No Error Check should check return value of exec 300
 MNER 3 No Error Check should check return value of exec 311
 MNER 3 No Error Check should check return value of exec 316
 MNER 3 No Error Check should check return value of exec 319
 MNER 3 No Error Check should check return value of exec 323
 MUCO 3 Useless Code Useless Brackets in create proc 18
 MUCO 3 Useless Code Useless Begin-End Pair 23
 MUCO 3 Useless Code Useless Brackets 335
 MTR1 2 Metrics: Comments Ratio Comments: 5% 17
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 12 = 11dec - 1exi + 2 17
 MTR3 2 Metrics: Query Complexity Complexity: 186 17

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_getmessage  
   reads table master..syslanguages (1)  
   reads table sybsystemprocs..sysusermessages  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..sysmessages (1)  

CALLERS
called by proc sybsystemprocs..sp_spaceusage_showhelp_all  
   called by proc sybsystemprocs..sp_spaceusage_showhelp  
      called by proc sybsystemprocs..sp_spaceusage