Database | Proc | Application | Created | Links |
sybsystemprocs | sp_clearstats | 31 Aug 14 | Defects Dependencies |
1 2 /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */ 3 /* 4.8 1.1 06/14/90 sproc/src/clearstats */ 4 5 /* 6 ** Messages for "sp_clearstats" 17900 7 ** 8 ** 17231, "No login with the specified name exists." 9 ** 17260, "Can't run %1! from within a transaction." 10 ** 17900, "%1! login account(s) cleared." 11 */ 12 13 /* 14 ** This stored procedure prints out Chargeback Accounting Statistics and 15 ** percentages using sp_reportstats. In addition, it resets the accounting 16 ** date, cpu usage, and I/O usage information. 17 */ 18 create procedure sp_clearstats 19 @loginame varchar(30) = NULL /* name of login to clear */ 20 as 21 22 declare @msg varchar(1024) 23 declare @cnt int, 24 @cputot int, 25 @iotot int 26 declare @msg_cnt varchar(10) 27 28 /* 29 ** If we're in a transaction, disallow this since it might make recovery 30 ** impossible. 31 */ 32 if @@trancount > 0 33 begin 34 /* 35 ** 17260, "Can't run %1! from within a transaction." 36 */ 37 raiserror 17260, "sp_clearstats" 38 return (1) 39 end 40 else 41 begin 42 set chained off 43 end 44 45 set transaction isolation level 1 46 47 /* check if user has sa role, proc_role will also do auditing 48 ** if required. proc_role will also print error message if required. 49 */ 50 51 if (proc_role("sa_role") = 0) 52 return (1) 53 54 if db_attr(db_name(), "durability") != 'full' 55 begin 56 /* 57 ** 17902, "You cannot run stored procedure '%1!' 58 ** from a low durbility database." 59 */ 60 raiserror 17902, "sp_clearstats" 61 return (1) 62 end 63 64 begin transaction clearuserstats 65 66 if @loginame is not NULL 67 begin 68 69 if not exists (select * from master.dbo.syslogins where name like @loginame and 70 ((status & 512) != 512)) /* not LOGIN PROFILE */ 71 begin 72 /* 73 ** 17231, "No login with the specified name exists." 74 */ 75 raiserror 17231 76 rollback transaction clearuserstats 77 return (1) 78 end 79 80 /* 81 ** Print out the statistics. 82 */ 83 execute sp_reportstats @loginame 84 85 /* 86 ** Update the login, setting the totcpu and totio columns to 0, and 87 ** the accdate to current time. 88 */ 89 update master.dbo.syslogins 90 set accdate = getdate(), totcpu = 0, totio = 0 91 where name like @loginame 92 end 93 else 94 begin 95 96 /* 97 ** Print out the statistics. 98 */ 99 execute sp_reportstats 100 101 /* 102 ** Update the login, setting the totcpu and totio columns to 0, and the 103 ** accdate to current time. 104 */ 105 update master.dbo.syslogins 106 set accdate = getdate(), totcpu = 0, totio = 0 107 108 end 109 /* 110 ** Check @@rowcount when it works 111 */ 112 select @cnt = @@rowcount 113 if @cnt > 0 114 begin 115 /* 116 ** 17900, "%1! login account(s) cleared." 117 */ 118 exec sp_getmessage 17900, @msg output 119 select @msg_cnt = rtrim(convert(char(10), @cnt)) 120 print @msg, @msg_cnt 121 end 122 commit transaction clearuserstats 123 return (0) 124
exec sp_procxmode 'sp_clearstats', 'AnyMode' go Grant Execute on sp_clearstats to public go
DEFECTS | |
QUDW 4 Update or delete with no where clause | 105 |
MGTP 3 Grant to public master..syslogins | |
MGTP 3 Grant to public sybsystemprocs..sp_clearstats | |
MNER 3 No Error Check should check return value of exec | 83 |
MNER 3 No Error Check should check @@error after update | 89 |
MNER 3 No Error Check should check return value of exec | 99 |
MNER 3 No Error Check should check @@error after update | 105 |
MNER 3 No Error Check should check return value of exec | 118 |
MUCO 3 Useless Code Useless Brackets | 38 |
MUCO 3 Useless Code Useless Brackets | 51 |
MUCO 3 Useless Code Useless Brackets | 52 |
MUCO 3 Useless Code Useless Brackets | 61 |
MUCO 3 Useless Code Useless Brackets | 77 |
MUCO 3 Useless Code Useless Brackets | 123 |
QISO 3 Set isolation level | 45 |
VUNU 3 Variable is not used @cputot | 24 |
VUNU 3 Variable is not used @iotot | 25 |
MSUB 2 Subquery Marker | 69 |
MTR1 2 Metrics: Comments Ratio Comments: 48% | 18 |
MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 5 = 7dec - 4exi + 2 | 18 |
MTR3 2 Metrics: Query Complexity Complexity: 43 | 18 |
DEPENDENCIES |
PROCS AND TABLES USED read_writes table master..syslogins (1) calls proc sybsystemprocs..sp_getmessage reads table master..sysmessages (1) reads table master..syslanguages (1) reads table sybsystemprocs..sysusermessages calls proc sybsystemprocs..sp_validlang reads table master..syslanguages (1) calls proc sybsystemprocs..sp_reportstats reads table master..syslogins (1) |