| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_spaceusage_object_genoutput | | 31 Aug 14 | Defects Dependencies |
1 2 /* 3 ** SP_SPACEUSAGE_OBJECT_GENOUTPUT 4 ** 5 ** The sub-procedure that prints on screen the space usage information on 6 ** the objects of interest in detail or summary based on action. It would 7 ** apply the SELECT list, WHERE clause and ORDER BY clause on the base 8 ** table #spaceusageinfo if the user has supplied any. Else, it will use 9 ** the default value for these. This is applicable only for "display", 10 ** "diplay summary", "report" and "report summary" actions. Called by 11 ** sp_spaceusage_object. 12 ** 13 ** Parameters 14 ** @actionword - The action to be performed. 15 ** @unit - Unit for the page counts. 16 ** @entity_type - Type of the entity. 17 ** @iname - Index name. 18 ** @select_list - SELECT list, if any, to project the ouput. 19 ** @where_clause - WHERE clause, if any, to select the output. 20 ** @order_by_clause- ORDER BY clause, if any, to order the output. 21 ** 22 ** Note: The @entity_type and @iname are passed because for entity type 23 ** "index" the #spaceusageinfo table has an additional data layer 24 ** row that qualifies even though they may not qualify (they may 25 ** qualify if the index name pattern included them) and are not 26 ** expected in the output result. They need to be filtered out 27 ** using the WHERE clause. 28 ** 29 ** Returns 30 ** 0 - if all goes well 31 ** other - error while execution 32 { 33 */ 34 create procedure sp_spaceusage_object_genoutput 35 ( 36 @actionword varchar(20) 37 , @unit varchar(6) 38 , @entity_type varchar(12) 39 , @iname varchar(255) 40 , @select_list varchar(1636) 41 , @where_clause varchar(1536) 42 , @order_by_clause varchar(768) 43 ) 44 as 45 begin -- { 46 47 declare @entity_table varchar(6) 48 , @entity_index varchar(6) 49 50 , @action_display varchar(8) 51 , @action_display_summary varchar(16) 52 , @action_report varchar(7) 53 , @action_report_summary varchar(15) 54 , @action_archive varchar(8) 55 56 , @summarytabname varchar(10) 57 , @sqlstmt varchar(600) 58 , @ptntabcount int 59 , @distinctdatecount int 60 , @archdatetime varchar(30) 61 , @returnStatus int 62 , @defaultselectlist varchar(175) 63 , @defaultorderby varchar(64) 64 , @whoami varchar(50) 65 , @msg varchar(256) 66 67 select @whoami = "sp_spaceusage_object_genoutput" 68 69 , @entity_table = "table" 70 , @entity_index = "index" 71 72 , @action_display = "display" 73 , @action_display_summary = "display summary" 74 , @action_report = "report" 75 , @action_report_summary = "report summary" 76 , @action_archive = "archive" 77 78 select @distinctdatecount = count(distinct (ArchiveDateTime)) 79 from #spaceusageinfo 80 where IndexName like @iname 81 82 /* 83 ** Sum over all the index and data layer space and 84 ** display/report the summary details to the user. 85 */ 86 if @actionword in (@action_display_summary, @action_report_summary) 87 begin -- { -- summary mode 88 89 if @entity_type = @entity_table 90 begin -- { -- table summary 91 92 select @summarytabname = "#summary1" 93 94 select @defaultorderby = 95 "ORDER BY TableName, OwnerName, Type" 96 + ", ArchiveDateTime" 97 98 if @order_by_clause is NULL 99 select @order_by_clause = @defaultorderby 100 101 select @select_list = case 102 when @actionword = 103 @action_report_summary 104 and @distinctdatecount > 1 105 then NULL 106 else -- exclude ArchiveDateTime 107 " OwnerName" 108 + ", TableName" 109 + ", Type" 110 + ", UsedPages" 111 + ", RsvdPages" 112 + ", ExpRsvdPages" 113 + ", PctBloatRsvdPages" 114 end 115 116 -- 1. Create the summary table and get the data layer 117 -- information in one go. 118 -- 119 -- NOTE: Two spaces with Type is deliberate to make 120 -- enough space for the other type 'INDEX'. 121 -- 122 select ArchiveDateTime 123 , OwnerName 124 , TableName 125 , Type = 'DATA ' 126 , UsedPages 127 , RsvdPages 128 , ExpRsvdPages 129 , PctBloatRsvdPages 130 into #summary1 131 from #spaceusageinfo 132 where IndId = 0 133 134 if (@@error != 0) 135 return (@@error) 136 137 -- 2.1. Insert a row for indid = 255 (TEXT data). 138 insert into #summary1 139 select ArchiveDateTime 140 , OwnerName 141 , TableName 142 , Type = 'TEXT ' 143 , UsedPages 144 , RsvdPages 145 , ExpRsvdPages 146 , PctBloatRsvdPages 147 from #spaceusageinfo 148 where IndId = 255 149 150 if (@@error != 0) 151 return (@@error) 152 153 -- 2.2. Insert the summ of all index layers. 154 insert into #summary1 155 select distinct ArchiveDateTime 156 , OwnerName 157 , TableName 158 , Type = 'INDEX' 159 , UsedPages = sum(UsedPages) 160 , RsvdPages = sum(RsvdPages) 161 , ExpRsvdPages = sum(ExpRsvdPages) 162 , PctBloatRsvdPages = 0.0 163 from #spaceusageinfo 164 where IndId > 0 165 and IndId != 255 166 group by ArchiveDateTime, Id 167 168 if (@@error != 0) 169 return (@@error) 170 171 -- 3. Compute the overall index layer percent bloat. 172 update #summary1 173 set PctBloatRsvdPages = 174 (convert(float, (RsvdPages 175 - ExpRsvdPages)) 176 / RsvdPages) * 100.0 177 where Type = 'INDEX' 178 and RsvdPages > 0 179 180 if (@@error != 0) 181 return (@@error) 182 183 end -- } -- table summary done! 184 else 185 begin -- { -- index summary 186 187 select @summarytabname = "#summary2" 188 189 select @defaultorderby = 190 "ORDER BY TableName, OwnerName, IndId" 191 + ", ArchiveDateTime" 192 193 if @order_by_clause is NULL 194 select @order_by_clause = @defaultorderby 195 196 select @select_list = case 197 when @actionword = 198 @action_report_summary 199 and @distinctdatecount > 1 200 then NULL 201 else -- exclude ArchiveDateTime 202 " OwnerName" 203 + ", TableName" 204 + ", IndexName" 205 + ", IndId" 206 + ", UsedPages" 207 + ", RsvdPages" 208 + ", ExpRsvdPages" 209 + ", PctBloatRsvdPages" 210 end 211 212 -- 1. Create the tempory summary index table. 213 select ArchiveDateTime 214 , OwnerName 215 , TableName 216 , IndexName 217 , IndId 218 , UsedPages 219 , RsvdPages 220 , ExpRsvdPages 221 , PctBloatRsvdPages 222 into #summary2 223 from #spaceusageinfo 224 where 1 = 0 225 226 if (@@error != 0) 227 return (@@error) 228 229 -- 2. Get the index level information. 230 select @sqlstmt = " INSERT INTO #summary2 " 231 + " SELECT DISTINCT ArchiveDateTime" 232 + ", OwnerName" 233 + ", TableName" 234 + ", IndexName" 235 + ", IndId" 236 + ", UsedPages=" 237 + "sum(UsedPages)" 238 + ", RsvdPages=" 239 + "sum(RsvdPages)" 240 + ", ExpRsvdPages=" 241 + "sum(ExpRsvdPages)" 242 + ", PctBloatRsvdPages=" 243 + "convert(float, 0) " 244 + " FROM #spaceusageinfo " 245 + " WHERE IndexName LIKE '" + @iname 246 + "'" 247 + " GROUP BY Id, IndId, ArchiveDateTime" 248 249 exec @returnStatus = sp_exec_SQL @sqlstmt, @whoami 250 251 if @returnStatus != 0 252 return (@returnStatus) 253 254 -- 3. Compute the overall index layer percent bloat. 255 update #summary2 256 set PctBloatRsvdPages = 257 (convert(float, (RsvdPages 258 - ExpRsvdPages)) 259 / RsvdPages) * 100.0 260 where RsvdPages > 0 261 262 if (@@error != 0) 263 return (@@error) 264 265 end -- } -- index summary done! 266 267 -- 4. Display the output. 268 269 -- Print information message regarding the unit specifier. 270 exec sp_getmessage 19533, @msg out 271 print @msg, @unit 272 273 -- Print information message regarding the datetimestamp, if 274 -- all data are dated same. 275 -- 276 if @distinctdatecount = 1 277 and @actionword = @action_report_summary 278 begin 279 select @archdatetime = max(ArchiveDateTime) 280 from #spaceusageinfo 281 where IndexName like @iname 282 283 exec sp_getmessage 19534, @msg out 284 print @msg, @archdatetime 285 end 286 287 set NOCOUNT on 288 289 exec @returnStatus = sp_autoformat @fulltabname = @summarytabname 290 , @selectlist = @select_list 291 , @whereclause = @where_clause 292 , @orderby = @order_by_clause 293 294 set NOCOUNT OFF 295 296 if @returnStatus != 0 297 return (@returnStatus) 298 else 299 return (0) 300 301 end -- } -- summary mode done 302 else 303 begin -- { -- normal mode 304 305 /* 306 ** Get the partitioned table count. If 0, we will skip PtnId 307 ** column while reporting. 308 */ 309 select @ptntabcount = count(*) 310 from #spaceusageinfo 311 where Id != PtnId 312 313 select @defaultselectlist = case 314 when @actionword = 315 @action_report 316 and @distinctdatecount > 1 317 then "ArchiveDateTime, " 318 else "" 319 end 320 + 321 "OwnerName, TableName, IndId" 322 + 323 case @entity_type 324 when @entity_index 325 then ", IndexName" 326 else "" 327 end 328 + 329 case 330 when @ptntabcount != 0 331 then ", PtnId" 332 else "" 333 end 334 + 335 case @entity_type 336 when @entity_table 337 then ", NumRows" 338 else "" 339 end 340 + 341 + ", UsedPages" 342 + ", RsvdPages, ExtentUtil" 343 + ", ExpRsvdPages" 344 + ", PctBloatUsedPages" 345 + ", PctBloatRsvdPages" 346 347 select @defaultorderby = "ORDER BY TableName, OwnerName, IndId" 348 + case 349 when @ptntabcount != 0 350 then ", PtnId" 351 else "" 352 end 353 + ", ArchiveDateTime" 354 355 if @select_list is NULL 356 begin 357 select @select_list = @defaultselectlist 358 end 359 else if ltrim(rtrim(@select_list)) = "*" 360 begin 361 362 -- We can not select all columns directly, as some of 363 -- them are temporary placeholders for intermediate data 364 -- and are not meant for display to the user. Hence, we 365 -- explicitly select ALL the columns to display. 366 -- 367 select @select_list = 368 " ArchiveDateTime, ServerName, MaxPageSize" 369 + ", DBName, OwnerName, TableName" 370 + ", Id, IndId, IndexName, PtnId, PtnName" 371 + ", DataPtnId, RowSize, NumRows, RowCount_ts" 372 + ", NumFwdRows, NumDelRows, EmptyPages" 373 + ", DataPages, UsedPages, RsvdPages, SpUtil" 374 + ", DPCR, DRCR, IPCR, LGIO, FF, MRPP, ERS" 375 + ", RPG, LeafPages, IndexHeight, OAMAPageCount" 376 + ", Extent0PageCount, Status, Sysstat" 377 + ", Sysstat2, NonLeafRowSize, ExpIndexHeight" 378 + ", ExpDataPages" 379 + ", ExpUsedPages, ExpRsvdPages, ExpLeafPages" 380 + ", PctBloatUsedPages, PctBloatRsvdPages" 381 + ", PctBloatLeafPages" 382 + ", ExtentUtil, PctEmptyPages, PctFwdRows" 383 + ", LockScheme, NumVarCols, HasAPLCI" 384 end 385 386 if @order_by_clause is NULL 387 select @order_by_clause = @defaultorderby 388 389 if @entity_type = @entity_index 390 begin 391 select @where_clause = @where_clause 392 + case @where_clause 393 when NULL 394 then " WHERE " 395 else " AND " 396 end 397 + " IndexName LIKE '" + @iname 398 + "'" 399 end 400 401 -- Print information message regarding the unit specifier. 402 exec sp_getmessage 19533, @msg out 403 print @msg, @unit 404 405 -- Print information message regarding the datetimestamp, if 406 -- all data are dated same. 407 -- 408 if @distinctdatecount = 1 409 and @actionword = @action_report 410 begin 411 select @archdatetime = max(ArchiveDateTime) 412 from #spaceusageinfo 413 where IndexName like @iname 414 415 exec sp_getmessage 19534, @msg out 416 print @msg, @archdatetime 417 end 418 419 set NOCOUNT on 420 421 exec @returnStatus = sp_autoformat @fulltabname = #spaceusageinfo 422 , @selectlist = @select_list 423 , @whereclause = @where_clause 424 , @orderby = @order_by_clause 425 426 set NOCOUNT OFF 427 428 if @returnStatus != 0 429 return (@returnStatus) 430 431 end -- } -- normal mode done! 432 433 end -- } -- } 434
exec sp_procxmode 'sp_spaceusage_object_genoutput', 'AnyMode' go Grant Execute on sp_spaceusage_object_genoutput to public go
| DEPENDENCIES |
| PROCS AND TABLES USED writes table tempdb..#summary2 (1) calls proc sybsystemprocs..sp_getmessage  reads table sybsystemprocs..sysusermessages reads table master..sysmessages (1)  calls proc sybsystemprocs..sp_validlang reads table master..syslanguages (1) reads table master..syslanguages (1) calls proc sybsystemprocs..sp_exec_SQL calls proc sybsystemprocs..sp_autoformat  reads table master..systypes (1) reads table master..syscolumns (1) read_writes table tempdb..#colinfo_af (1) calls proc sybsystemprocs..sp_autoformat reads table tempdb..syscolumns (1)  reads table tempdb..systypes (1) calls proc sybsystemprocs..sp_namecrack writes table tempdb..#summary1 (1) reads table tempdb..#spaceusageinfo (1) CALLERS called by proc sybsystemprocs..sp_spaceusage_object called by proc sybsystemprocs..sp_spaceusage |
