DatabaseProcApplicationCreatedLinks
sybsystemprocssp_spaceusage_cnvtounit  31 Aug 14Defects Dependencies

1     
2     /*
3     ** Generated by spgenmsgs.pl on Tue Oct 31 23:35:57 2006 
4     */
5     /*
6     ** raiserror Messages for spaceusage_cnvtounit [Total 0]
7     */
8     /*
9     ** sp_getmessage Messages for spaceusage_cnvtounit [Total 0]
10    */
11    /*
12    ** End spgenmsgs.pl output.
13    */
14    /*
15    **	SP_SPACEUSAGE_CNVTOUNIT
16    **
17    **	The sub-procedure takes as input a table name and a unit specifier and
18    **	computes the value for the page count columns in the input table into
19    **	the specified units, from the corresponding raw page count columns in
20    **	the input table.
21    **	
22    **	Parameters
23    **		@unit	- A string that gives the unit into which to convert.
24    **			  Currently, only "KB", "MB", "GB" or "PAGES" is 
25    **			  supported.
26    **		@tabname - The table for which to do the computation.
27    ** 	
28    **	Returns
29    **		0 - if all goes well
30    **		1 - if the table name is invalid
31    **	    other - error while execution 
32    {
33    */
34    create procedure sp_spaceusage_cnvtounit
35    (
36        @unit varchar(6)
37        , @tabname varchar(255)
38    )
39    as
40        begin -- {
41    
42            declare @factorBy float
43                , @factorBy_str varchar(20)
44                , @bytesPerUnit float
45                , @sqlstmt varchar(650)
46                , @objecttmptab varchar(20)
47                , @tranlogtmptab varchar(20)
48                , @whoami varchar(50)
49                , @returnStatus int
50    
51            select @unit = upper(ltrim(rtrim(@unit)))
52                , @objecttmptab = "#spaceusageinfo"
53                , @tranlogtmptab = "#syslogsinfo"
54                , @whoami = "sp_spaceusage_cnvtounit"
55    
56            -- The proc assumes that only valid unit specifier is supplied through
57            -- the @unit paramater and this has been checked/verfied before calling
58            -- this proc. It, however, tries to not do anything in case unit
59            -- specifier is not supported.
60            --
61            -- NOTE: If higher units are added, then the @bytesPerUnit variable
62            -- might overflow. For example, "TB". 
63            --
64            select @bytesPerUnit = case @unit
65                    when "KB" then 1024.0
66                    when "MB" then 1024.0 * 1024.0
67                    when "GB" then 1024.0 * 1024.0 * 1024.0
68                    when "PAGES" then 1.0
69                    else @@maxpagesize
70                end
71    
72            select @factorBy = case @unit
73                    when "PAGES" then 1.0
74                    else @@maxpagesize / @bytesPerUnit
75                end
76    
77            select @factorBy_str = convert(varchar, @factorBy)
78    
79            select @sqlstmt = "UPDATE " + @tabname + " SET "
80    
81            if lower(ltrim(rtrim(@tabname))) = @objecttmptab
82            begin -- {
83    
84                select @sqlstmt = @sqlstmt + " DataPages=DataPageCount*"
85                    + @factorBy_str
86                    + ", UsedPages=UsedPageCount*"
87                    + @factorBy_str
88                    + ", RsvdPages=RsvdPageCount*"
89                    + @factorBy_str
90                    + ", EmptyPages=EmptyPageCount*"
91                    + @factorBy_str
92                    + ", LeafPages=LeafPageCount*"
93                    + @factorBy_str
94                    + ", ExpDataPages=ExpDataPageCount*"
95                    + @factorBy_str
96                    + ", ExpUsedPages=ExpUsedPageCount*"
97                    + @factorBy_str
98                    + ", ExpRsvdPages=ExpRsvdPageCount*"
99                    + @factorBy_str
100                   + ", ExpLeafPages=ExpLeafPageCount*"
101                   + @factorBy_str
102   
103   
104           end -- }
105           else if lower(ltrim(rtrim(@tabname))) = @tranlogtmptab
106           begin -- {
107   
108               select @sqlstmt = @sqlstmt + " LCTPages=LCTPageCount*"
109                   + @factorBy_str
110                   + ", TotalPages=TotalPageCount*"
111                   + @factorBy_str
112                   + ", CLRPages=CLRPageCount*"
113                   + @factorBy_str
114                   + ", UsedPages=UsedPageCount*"
115                   + @factorBy_str
116                   + ", FreePages=FreePageCount*"
117                   + @factorBy_str
118   
119           end -- }
120           else -- invalid table name
121               return (1)
122   
123           exec @returnStatus = sp_exec_SQL @sqlstmt, @whoami
124   
125           return (@returnStatus)
126   
127       end -- }	-- }
128   


exec sp_procxmode 'sp_spaceusage_cnvtounit', 'AnyMode'
go

Grant Execute on sp_spaceusage_cnvtounit to public
go
DEFECTS
 MTYP 4 Assignment type mismatch @callerID: varchar(30) = varchar(50) 123
 MGTP 3 Grant to public sybsystemprocs..sp_spaceusage_cnvtounit  
 MNER 3 No Error Check should check return value of exec 123
 MUCO 3 Useless Code Useless Brackets in create proc 35
 MUCO 3 Useless Code Useless Begin-End Pair 40
 MUCO 3 Useless Code Useless Brackets 121
 MUCO 3 Useless Code Useless Brackets 125
 MTR1 2 Metrics: Comments Ratio Comments: 34% 34
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 2 = 2dec - 2exi + 2 34
 MTR3 2 Metrics: Query Complexity Complexity: 16 34

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_exec_SQL  

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