Database	Proc	Application	Created	Links
sybsystemprocs	sp_spaceusage_tranlog_genoutput		31 Aug 14	Defects Dependencies

1     
2     /*
3     **	SP_SPACEUSAGE_TRANLOG_GENOUTPUT
4     **	
5     **	The sub-procedure that prints on screen the space usage information on
6     **	the transaction log. It would apply the SELECT list, WHERE clause and 
7     **	ORDER BY clause on the base table #syslogsinfo if the user has supplied
8     **	any. Else, it will use the default value for these. This is applicable 
9     **	only for "display" and "report" actions. Called by
10    **	sp_spaceusage_tranlog.
11    **
12    **	Parameters
13    **		@actionword 	- The action to be performed.
14    **		@unit		- Unit for the page counts.
15    **		@select_list	- Select list, if any, to project the ouput.
16    **		@where_clause 	- Where clause, if any, to select the output.
17    **		@order_by_clause- Order by clause, if any, to order the output.
18    **
19    **	Returns
20    **		0 - if all goes well
21    **	    other - error while execution 
22    {
23    */
24    create procedure sp_spaceusage_tranlog_genoutput
25    (
26        @actionword varchar(20)
27        , @unit varchar(6)
28        , @select_list varchar(1536)
29        , @where_clause varchar(1536)
30        , @order_by_clause varchar(768)
31    )
32    as
33        begin -- {	
34    
35            declare
36                @action_display varchar(8)
37                , @action_report varchar(7)
38    
39                , @distinctdatecount int
40                , @archdatetime varchar(30)
41                , @returnStatus int
42                , @defaultselectlist varchar(116)
43                , @defaultorderby varchar(50)
44                , @whoami varchar(50)
45                , @msg varchar(256)
46    
47            select @whoami = "sp_spaceusage_tranlog_genoutput"
48    
49                , @action_display = "display"
50                , @action_report = "report"
51    
52            select @distinctdatecount = count(distinct (ArchiveDateTime))
53            from #syslogsinfo
54    
55            select @defaultselectlist = case
56                    when @actionword = @action_report
57                        and @distinctdatecount > 1
58                    then "ArchiveDateTime, "
59                    else ""
60                end
61                + " TableName, TotalPages, UsedPages"
62                + ", CLRPages, FreePages, PctUsedPages"
63                + ", PctFreePages"
64    
65            select @defaultorderby =
66                "order by TableName, OwnerName, ArchiveDateTime"
67    
68            if @select_list is NULL
69            begin
70                select @select_list = @defaultselectlist
71            end
72            else if ltrim(rtrim(@select_list)) = "*"
73            begin
74                select @select_list =
75                    " ArchiveDateTime, ServerName, MaxPageSize, DBName"
76                    + ", OwnerName, TableName, Id, IsMLD, IsLogFull"
77                    + ", LCTPages, TotalPages, CLRPages, UsedPages"
78                    + ", FreePages, PctFreePages, PctUsedPages"
79    
80            end
81    
82            if @order_by_clause is NULL
83                select @order_by_clause = @defaultorderby
84    
85            set NOCOUNT on
86    
87            exec sp_getmessage 19533, @msg out
88            print @msg, @unit
89    
90            if @distinctdatecount = 1 and @actionword = @action_report
91            begin
92                select @archdatetime = max(ArchiveDateTime)
93                from #syslogsinfo
94    
95                exec sp_getmessage 19534, @msg out
96                print @msg, @archdatetime
97            end
98    
99            exec @returnStatus = sp_autoformat @fulltabname = #syslogsinfo
100               , @selectlist = @select_list
101               , @whereclause = @where_clause
102               , @orderby = @order_by_clause
103   
104           set NOCOUNT off
105   
106           if @returnStatus != 0
107               return (@returnStatus)
108   
109           return (0)
110   
111       end -- }	-- }
112



exec sp_procxmode 'sp_spaceusage_tranlog_genoutput', 'AnyMode'
go

Grant Execute on sp_spaceusage_tranlog_genoutput to public
go

DEFECTS
MEST 4 Empty String will be replaced by Single Space	59
MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname	99
MGTP 3 Grant to public sybsystemprocs..sp_spaceusage_tranlog_genoutput
MNER 3 No Error Check should check return value of exec	87
MNER 3 No Error Check should check return value of exec	95
MNER 3 No Error Check should check return value of exec	99
MUCO 3 Useless Code Useless Brackets in create proc	25
MUCO 3 Useless Code Useless Begin-End Pair	33
MUCO 3 Useless Code Useless Brackets	52
MUCO 3 Useless Code Useless Brackets	107
MUCO 3 Useless Code Useless Brackets	109
VNRD 3 Variable is not read @whoami	47
VNRD 3 Variable is not read @action_display	49
MTR1 2 Metrics: Comments Ratio Comments: 26%	24
MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 8 = 7dec - 1exi + 2	24
MTR3 2 Metrics: Query Complexity Complexity: 34	24