Database | Proc | Application | Created | Links |
sybsystemprocs | sp_spaceusage_tranlog_archdata | 31 Aug 14 | Defects 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 |