DatabaseProcApplicationCreatedLinks
sybsystemprocssp_clearstats  31 Aug 14Defects 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)