DatabaseProcApplicationCreatedLinks
sybsystemprocssp_spaceusage_object_genoutput  14 déc. 14Defects Propagation 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 or replace 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
DEFECTS
 QBGB 6 Bad group by OwnerName 156
 MEST 4 Empty String will be replaced by Single Space 318
 MEST 4 Empty String will be replaced by Single Space 326
 MEST 4 Empty String will be replaced by Single Space 332
 MEST 4 Empty String will be replaced by Single Space 338
 MEST 4 Empty String will be replaced by Single Space 351
 MTYP 4 Assignment type mismatch @callerID: varchar(30) = varchar(50) 249
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 421
 MGTP 3 Grant to public sybsystemprocs..sp_spaceusage_object_genoutput  
 MNEJ 3 'Not Equal' join 311
 MNER 3 No Error Check should check return value of exec 270
 MNER 3 No Error Check should check return value of exec 283
 MNER 3 No Error Check should check return value of exec 289
 MNER 3 No Error Check should check return value of exec 402
 MNER 3 No Error Check should check return value of exec 415
 MNER 3 No Error Check should check return value of exec 421
 MUCO 3 Useless Code Useless Brackets in create proc 35
 MUCO 3 Useless Code Useless Begin-End Pair 45
 MUCO 3 Useless Code Useless Brackets 78
 MUCO 3 Useless Code Useless Brackets 134
 MUCO 3 Useless Code Useless Brackets 135
 MUCO 3 Useless Code Useless Brackets 150
 MUCO 3 Useless Code Useless Brackets 151
 MUCO 3 Useless Code Useless Brackets 168
 MUCO 3 Useless Code Useless Brackets 169
 MUCO 3 Useless Code Useless Brackets 180
 MUCO 3 Useless Code Useless Brackets 181
 MUCO 3 Useless Code Useless Brackets 226
 MUCO 3 Useless Code Useless Brackets 227
 MUCO 3 Useless Code Useless Brackets 252
 MUCO 3 Useless Code Useless Brackets 262
 MUCO 3 Useless Code Useless Brackets 263
 MUCO 3 Useless Code Useless Brackets 297
 MUCO 3 Useless Code Useless Brackets 299
 MUCO 3 Useless Code Useless Brackets 429
 QCTC 3 Conditional Table Creation 122
 QCTC 3 Conditional Table Creation 213
 QGWO 3 Group by/Distinct/Union without order by 155
 QPNC 3 No column in condition 224
 VNRD 3 Variable is not read @action_display 72
 VNRD 3 Variable is not read @action_archive 76
 MTR1 2 Metrics: Comments Ratio Comments: 24% 34
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 16 = 25dec - 11exi + 2 34
 MTR3 2 Metrics: Query Complexity Complexity: 127 34

DATA PROPAGATION detailed
ColumnWritten To
@inamesp_autoformat_rset_002._clid#af sp_exec_SQL_rset_001.sqlbNoName57
@select_listsp_autoformat_rset_001._maxlen#af °._asname#af sp_autoformat_rset_002._clid#af sp_autoformat_rset_003._asname#af
@where_clausesp_autoformat_rset_002._clid#af

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_exec_SQL  
   writes table sybsystemprocs..sp_exec_SQL_rset_001 
writes table tempdb..#summary1 (1) 
writes table tempdb..#summary2 (1) 
calls proc sybsystemprocs..sp_autoformat  
   writes table sybsystemprocs..sp_autoformat_rset_002 
   reads table master..systypes (1)  
   writes table sybsystemprocs..sp_autoformat_rset_004 
   calls proc sybsystemprocs..sp_autoformat  
   reads table master..syscolumns (1)  
   read_writes table tempdb..#colinfo_af (1) 
   reads table tempdb..systypes (1)  
   writes table sybsystemprocs..sp_autoformat_rset_001 
   reads table tempdb..syscolumns (1)  
   writes table sybsystemprocs..sp_autoformat_rset_003 
   calls proc sybsystemprocs..sp_namecrack  
   writes table sybsystemprocs..sp_autoformat_rset_005 
reads table tempdb..#spaceusageinfo (1) 
calls proc sybsystemprocs..sp_getmessage  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..syslanguages (1)  
   reads table sybsystemprocs..sysusermessages  
   reads table master..sysmessages (1)  

CALLERS
called by proc sybsystemprocs..sp_spaceusage_object  
   called by proc sybsystemprocs..sp_spaceusage