| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_spaceusage_showhelp_report | | 31 Aug 14 | Defects 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 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
| 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  |
