DatabaseProcApplicationCreatedLinks
sybsystemprocssp_spaceusage_showhelp_archive  31 Aug 14Defects Dependencies

1     
2     /*
3     **	SP_SPACEUSAGE_SHOWHELP_ARCHIVE
4     **
5     **	The sub-procedure to print "archive" 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 procedure sp_spaceusage_showhelp_archive
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 19591, @msg output
40                print @msg
41                print ""
42    
43                exec sp_getmessage 18954, @msg output
44                print @msg
45    
46                print "sp_spaceusage 'archive', {'table'|'index'}, <name>"
47                print "		[,<where_clause> [,<command>]]"
48                print ""
49                print "sp_spaceusage 'archive', 'tranlog' [,{'syslogs'|NULL}"
50                print "		[,<where_clause>]]"
51                print ""
52    
53                exec sp_getmessage 19539, @msg out
54                print @msg
55    
56                print "sp_spaceusage 'help', 'archive', 'table'"
57                print "sp_spaceusage 'help', 'archive', 'index'"
58                print "sp_spaceusage 'help', 'archive', 'tranlog'"
59                print ""
60                print ""
61    
62            end -- }
63    
64            select @pattern = case @helpentity
65                    when "index"
66                    then @pattern4table + "." + @pattern4index
67                    else @pattern4table
68                end
69    
70            if @helpentity in ("table", "index")
71            begin -- {
72    
73                exec sp_getmessage 19540, @msg out
74                print @msg, @helpentity
75    
76                exec sp_getmessage 18954, @msg output
77                print @msg
78    
79                print "sp_spaceusage 'archive [using {<using_item>[,<using_item>]...} ]', '%1!', <name>"
80                    , @helpentity
81                print "		[[,where_clause] [,command]"
82                print ""
83                print "%1!<using_item> :: { unit={KB|MB|GB|PAGES} | dbname=<db> | prefix=<string> }"
84                    , "	"
85                print ""
86    
87                exec sp_getmessage 19541, @msg out
88                print @msg
89                print ""
90    
91                if @helpentity = "table"
92                begin
93                    exec sp_getmessage 19559, @msg out
94                end
95                else
96                begin
97                    exec sp_getmessage 19560, @msg out
98                end
99                print @msg, "  -- ", NULL, "MB", "nc"
100   
101               exec sp_getmessage 19546, @msg out
102               print @msg, "  -- ", "my", "'user1'"
103   
104               exec sp_getmessage 19561, @msg out
105               print @msg, "  -- ", "spaceusage_object"
106   
107               print "  -- "
108               print "  sp_spaceusage 'archive using unit=MB', '%1!', 'user1.%2!'"
109                   , @helpentity, @pattern
110   
111               print ""
112   
113               if @helpentity = "table"
114               begin
115                   exec sp_getmessage 19559, @msg out
116               end
117               else
118               begin
119                   exec sp_getmessage 19560, @msg out
120               end
121               print @msg, "  -- ", NULL, "KB", "nc"
122   
123               exec sp_getmessage 19546, @msg out
124               print @msg, "  -- ", "my", "'user1'"
125   
126               exec sp_getmessage 19562, @msg out
127               print @msg, "  -- ", "spaceusage_object", "mydb"
128   
129               exec sp_getmessage 19547, @msg out
130               print @msg, "  -- ", "update table statistics"
131   
132               print "  -- "
133               print "  sp_spaceusage 'archive using dbname=mydb', '%1!', 'user1.%2!', NULL,"
134                   , @helpentity, @pattern
135               print "		 'update table statistics'"
136   
137               print ""
138   
139               if @helpentity = "table"
140               begin
141                   exec sp_getmessage 19559, @msg out
142               end
143               else
144               begin
145                   exec sp_getmessage 19560, @msg out
146               end
147               print @msg, "  -- ", NULL, "KB", "nc"
148   
149               exec sp_getmessage 19546, @msg out
150               print @msg, "  -- ", "my", "'dbo'"
151   
152               exec sp_getmessage 19561, @msg out
153               print @msg, "  -- ", "dbo_gt40pct_spaceusage_object"
154   
155               exec sp_getmessage 19548, @msg out
156               print @msg, "  -- ", "PctBloatRsvdPages > 40"
157   
158               print "  -- "
159               print "  sp_spaceusage 'archive using prefix=dbo_gt40pct_', '%1!', 'dbo.%2!',"
160                   , @helpentity, @pattern
161               print "		 'where PctBloatRsvdPages > 40'"
162   
163               print ""
164   
165               if @helpentity = "table"
166               begin
167                   exec sp_getmessage 19559, @msg out
168               end
169               else
170               begin
171                   exec sp_getmessage 19560, @msg out
172               end
173               print @msg, "  -- ", NULL, "KB", "nc"
174   
175               exec sp_getmessage 19550, @msg out
176               print @msg, "  -- ", "my"
177   
178               exec sp_getmessage 19562, @msg out
179               print @msg, "  -- ", "gt100pct_spaceusage_object", "mydb"
180   
181               exec sp_getmessage 19548, @msg out
182               print @msg, "  -- ", "PctBloatRsvdPages > 40"
183   
184               exec sp_getmessage 19547, @msg out
185               print @msg, "  -- ", "update table statistics"
186   
187               select @pattern = "%." + @pattern
188   
189               print "  -- "
190               print "  sp_spaceusage 'archive using dbname=mydb,prefix=all_gt100pct_', '%1!', '%2!',"
191                   , @helpentity, @pattern
192               print "		 'where PctBloatRsvdPages > 100', 'update table statistics'"
193   
194               print ""
195   
196           end -- }
197           else if @helpentity = "tranlog"
198           begin -- {
199   
200               exec sp_getmessage 19540, @msg out
201               print @msg, @helpentity
202   
203               exec sp_getmessage 18954, @msg output
204               print @msg
205   
206               print "sp_spaceusage 'archive [using {<using_item>[,<using_item>]...} ]', 'tranlog' [,{'syslogs'|NULL}"
207               print "		[,where_clause]]"
208               print ""
209               print "%1!<using_item> :: { unit={KB|MB|GB|PAGES} | dbname=<db> | prefix=<string> }"
210                   , "	"
211               print ""
212   
213               exec sp_getmessage 19541, @msg out
214               print @msg
215               print ""
216   
217               exec sp_getmessage 19559, @msg out
218               select @msg = @msg + " transaction log"
219               print @msg, "  -- ", NULL, "MB"
220   
221               exec sp_getmessage 19561, @msg out
222               print @msg, "  -- ", "spaceusage_tranlog"
223   
224               print "  -- "
225               print "  sp_spaceusage 'archive using unit=MB', 'tranlog', 'syslogs'"
226   
227               print ""
228   
229               exec sp_getmessage 19559, @msg out
230               select @msg = @msg + " transaction log"
231               print @msg, "  -- ", NULL, "KB"
232   
233               exec sp_getmessage 19561, @msg out
234               print @msg, "  -- ", "canupgrd_spaceusage_tranlog"
235   
236               exec sp_getmessage 19548, @msg out
237               print @msg, "  -- ", "HasSpaceForUpgrade = 1"
238   
239               print "  -- "
240               print "  sp_spaceusage 'archive using prefix=canupgrd_', 'tranlog', NULL,"
241               print "		 'where HasSpaceForUpgrade = 1'"
242   
243               print ""
244   
245               exec sp_getmessage 19559, @msg out
246               select @msg = @msg + " transaction log"
247               print @msg, "  -- ", NULL, "KB"
248   
249               exec sp_getmessage 19562, @msg out
250               print @msg, "  -- ", "mld_spaceusage_tranlog", "mydb"
251   
252               exec sp_getmessage 19548, @msg out
253               print @msg, "  -- ", "IsMLD = 1"
254   
255               print "  -- "
256               print "  sp_spaceusage 'archive using dbname=mydb,prefix=mld_', 'tranlog', NULL,"
257               print "		 'where IsMLD = 1'"
258   
259               print ""
260   
261           end -- }
262   
263           return (0)
264   
265       end -- }	-- }
266   


exec sp_procxmode 'sp_spaceusage_showhelp_archive', 'AnyMode'
go

Grant Execute on sp_spaceusage_showhelp_archive 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 59
 MEST 4 Empty String will be replaced by Single Space 60
 MEST 4 Empty String will be replaced by Single Space 82
 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 111
 MEST 4 Empty String will be replaced by Single Space 137
 MEST 4 Empty String will be replaced by Single Space 163
 MEST 4 Empty String will be replaced by Single Space 194
 MEST 4 Empty String will be replaced by Single Space 208
 MEST 4 Empty String will be replaced by Single Space 211
 MEST 4 Empty String will be replaced by Single Space 215
 MEST 4 Empty String will be replaced by Single Space 227
 MEST 4 Empty String will be replaced by Single Space 243
 MEST 4 Empty String will be replaced by Single Space 259
 MGTP 3 Grant to public sybsystemprocs..sp_spaceusage_showhelp_archive  
 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 53
 MNER 3 No Error Check should check return value of exec 73
 MNER 3 No Error Check should check return value of exec 76
 MNER 3 No Error Check should check return value of exec 87
 MNER 3 No Error Check should check return value of exec 93
 MNER 3 No Error Check should check return value of exec 97
 MNER 3 No Error Check should check return value of exec 101
 MNER 3 No Error Check should check return value of exec 104
 MNER 3 No Error Check should check return value of exec 115
 MNER 3 No Error Check should check return value of exec 119
 MNER 3 No Error Check should check return value of exec 123
 MNER 3 No Error Check should check return value of exec 126
 MNER 3 No Error Check should check return value of exec 129
 MNER 3 No Error Check should check return value of exec 141
 MNER 3 No Error Check should check return value of exec 145
 MNER 3 No Error Check should check return value of exec 149
 MNER 3 No Error Check should check return value of exec 152
 MNER 3 No Error Check should check return value of exec 155
 MNER 3 No Error Check should check return value of exec 167
 MNER 3 No Error Check should check return value of exec 171
 MNER 3 No Error Check should check return value of exec 175
 MNER 3 No Error Check should check return value of exec 178
 MNER 3 No Error Check should check return value of exec 181
 MNER 3 No Error Check should check return value of exec 184
 MNER 3 No Error Check should check return value of exec 200
 MNER 3 No Error Check should check return value of exec 203
 MNER 3 No Error Check should check return value of exec 213
 MNER 3 No Error Check should check return value of exec 217
 MNER 3 No Error Check should check return value of exec 221
 MNER 3 No Error Check should check return value of exec 229
 MNER 3 No Error Check should check return value of exec 233
 MNER 3 No Error Check should check return value of exec 236
 MNER 3 No Error Check should check return value of exec 245
 MNER 3 No Error Check should check return value of exec 249
 MNER 3 No Error Check should check return value of exec 252
 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 263
 MTR1 2 Metrics: Comments Ratio Comments: 6% 17
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 10 = 9dec - 1exi + 2 17
 MTR3 2 Metrics: Query Complexity Complexity: 145 17

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

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