DatabaseProcApplicationCreatedLinks
sybsystemprocssp_spaceusage_tranlog_archdata  31 Aug 14Defects Dependencies

1     
2     /*
3     **	SP_SPACEUSAGE_TRANLOG_ARCHDATA
4     **
5     **	The sub-procedure that saves the space usage information in the
6     **	temporary table #syslogsinfo into an archive table. This is
7     **	applicable only in case of "archive" action. It is possible to apply a
8     **	WHERE clause, if any, to selectively archive only the data of interest.
9     **	Called by sp_spaceusage_tranlog.
10    **
11    **	Parameters
12    **		@archivetabname	- The 3-part name of the archive table.
13    **		@where_clause	- Where clause, if any, to select the data.
14    **
15    **	Returns
16    **		0 - if all goes well
17    **	    other - error while execution 
18    {
19    */
20    create procedure sp_spaceusage_tranlog_archdata
21    (
22        @archivetabname varchar(320)
23        , @where_clause varchar(1536)
24    )
25    as
26        begin -- {	
27    
28            declare @sqlstmt varchar(2048)
29                , @returnStatus int
30                , @whoami varchar(50)
31                , @msg varchar(256)
32                , @colsToArchive varchar(256)
33    
34            select @whoami = "sp_spaceusage_tranlog_archdata"
35    
36            /* Explicitly set the list of  columns to archive. */
37            select @colsToArchive = "  VersionNum, ESDNum, EBFNum"
38                + ", ArchiveDateTime, ServerName, MaxPageSize"
39                + ", DBName, OwnerName, TableName, Id, NumRows"
40                + ", IsMLD, IsLogFull, LCTPageCount"
41                + ", TotalPageCount, RsvdPageCount"
42                + ", CLRPageCount, UsedPageCount"
43                + ", FreePageCount, PctFreePages, PctUsedPages"
44    
45            /* 
46            ** If archive table does not exist, create it by SELECTing
47            ** #syslogsinfo INTO int it. Else, INSERT #syslogsinfo into it.
48            */
49            if object_id(@archivetabname) is NULL
50            begin
51                select @sqlstmt = " SELECT " + @colsToArchive
52                    + " INTO " + @archivetabname
53                    + " FROM   #syslogsinfo "
54                    + @where_clause
55            end
56            else
57            begin
58                select @sqlstmt = " INSERT INTO " + @archivetabname
59                    + " SELECT " + @colsToArchive
60                    + " FROM   #syslogsinfo "
61                    + @where_clause
62            end
63    
64            exec @returnStatus = sp_exec_SQL @sqlstmt, @whoami
65    
66            if @returnStatus != 0
67            begin
68                return (@returnStatus)
69            end
70            else
71            begin
72                exec sp_getmessage 19535, @msg out
73                print @msg, @archivetabname
74                return (0)
75            end
76    
77        end -- }	-- }
78    


exec sp_procxmode 'sp_spaceusage_tranlog_archdata', 'AnyMode'
go

Grant Execute on sp_spaceusage_tranlog_archdata to public
go
DEFECTS
 MTYP 4 Assignment type mismatch @callerID: varchar(30) = varchar(50) 64
 MGTP 3 Grant to public sybsystemprocs..sp_spaceusage_tranlog_archdata  
 MNER 3 No Error Check should check return value of exec 72
 MUCO 3 Useless Code Useless Brackets in create proc 21
 MUCO 3 Useless Code Useless Begin-End Pair 26
 MUCO 3 Useless Code Useless Brackets 68
 MUCO 3 Useless Code Useless Brackets 74
 MTR1 2 Metrics: Comments Ratio Comments: 37% 20
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 1 = 2dec - 3exi + 2 20
 MTR3 2 Metrics: Query Complexity Complexity: 15 20

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)  
calls proc sybsystemprocs..sp_exec_SQL  

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