DatabaseProcApplicationCreatedLinks
sybsystemprocssp_clearstats  14 déc. 14Defects Propagation 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    ** 17902, "You cannot run stored procedure '%1!' from a %2! database."
12    ** 17168, "low durability"
13    */
14    
15    /*
16    **  This stored procedure prints out Chargeback Accounting Statistics and
17    **  percentages using sp_reportstats. In addition, it resets the accounting
18    **  date, cpu usage, and I/O usage information.
19    */
20    create or replace procedure sp_clearstats
21        @loginame varchar(30) = NULL /* name of login to clear */
22    as
23    
24        declare @msg varchar(1024)
25        declare @cnt int,
26            @cputot int,
27            @iotot int
28        declare @msg_cnt varchar(10)
29        declare @nullarg char(1)
30        declare @dummy int
31        declare @status int
32        declare @gp_enabled int
33    
34    
35    
36        /*
37        **  If we're in a transaction, disallow this since it might make recovery
38        **  impossible.
39        */
40        if @@trancount > 0
41        begin
42            /*
43            ** 17260, "Can't run %1! from within a transaction."
44            */
45            raiserror 17260, "sp_clearstats"
46            return (1)
47        end
48        else
49        begin
50            set chained off
51        end
52    
53        set transaction isolation level 1
54    
55        /* 
56        ** If granular permissions is not enabled check if user has sa role, 
57        ** proc_role will also do auditing
58        ** if required. proc_role will also print error message if required.
59        ** If granular permissions is enabled check to see if user has 'manage server'
60        ** permission.  proc_auditperm will also auditing if required. proc_auditperm 
61        ** will also print error message if required.
62        */
63        select @nullarg = NULL
64        execute @status = sp_aux_checkroleperm "sa_role",
65            "manage server", @nullarg, @gp_enabled output
66    
67        /* For Auditing */
68        if (@gp_enabled = 0)
69        begin
70            if (proc_role("sa_role") = 0)
71            begin
72                return 1
73            end
74        end
75        else
76        begin
77            select @dummy = proc_auditperm("manage server", @status)
78        end
79        if (@status != 0)
80            return (1)
81    
82        if db_attr(db_name(), "durability") != 'full'
83        begin
84            /*
85            ** 17902, "You cannot run stored procedure '%1!' from a %2! database."
86            ** 17168, "low durability"
87            */
88            raiserror 17902, "sp_clearstats", "low durability"
89            return (1)
90        end
91    
92        begin transaction clearuserstats
93    
94        if @loginame is not NULL
95        begin
96    
97            if not exists (select * from master.dbo.syslogins where name like @loginame and
98                        ((status & 512) != 512)) /* not LOGIN PROFILE */
99            begin
100               /*
101               ** 17231, "No login with the specified name exists."
102               */
103               raiserror 17231
104               rollback transaction clearuserstats
105               return (1)
106           end
107   
108           /*
109           **  Print out the statistics.
110           */
111           execute sp_reportstats @loginame
112   
113           /*
114           **  Update the login, setting the totcpu and totio columns to 0, and
115           **  the accdate to current time.
116           */
117           update master.dbo.syslogins
118           set accdate = getdate(), totcpu = 0, totio = 0
119           where name like @loginame
120       end
121       else
122       begin
123   
124           /*
125           **  Print out the statistics.
126           */
127           execute sp_reportstats
128   
129           /*
130           **  Update the login, setting the totcpu and totio columns to 0, and the
131           **  accdate to current time.
132           */
133           update master.dbo.syslogins
134           set accdate = getdate(), totcpu = 0, totio = 0
135   
136       end
137       /*
138       **  Check @@rowcount when it works
139       */
140       select @cnt = @@rowcount
141       if @cnt > 0
142       begin
143           /*
144           ** 17900, "%1! login account(s) cleared."
145           */
146           exec sp_getmessage 17900, @msg output
147           select @msg_cnt = rtrim(convert(char(10), @cnt))
148           print @msg, @msg_cnt
149       end
150       commit transaction clearuserstats
151       return (0)
152   


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 133
 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 111
 MNER 3 No Error Check should check @@error after update 117
 MNER 3 No Error Check should check return value of exec 127
 MNER 3 No Error Check should check @@error after update 133
 MNER 3 No Error Check should check return value of exec 146
 MUCO 3 Useless Code Useless Brackets 46
 MUCO 3 Useless Code Useless Brackets 68
 MUCO 3 Useless Code Useless Brackets 70
 MUCO 3 Useless Code Useless Brackets 79
 MUCO 3 Useless Code Useless Brackets 80
 MUCO 3 Useless Code Useless Brackets 89
 MUCO 3 Useless Code Useless Brackets 105
 MUCO 3 Useless Code Useless Brackets 151
 QISO 3 Set isolation level 53
 VNRD 3 Variable is not read @dummy 77
 VUNU 3 Variable is not used @cputot 26
 VUNU 3 Variable is not used @iotot 27
 MSUB 2 Subquery Marker 97
 MTR1 2 Metrics: Comments Ratio Comments: 48% 20
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 7 = 10dec - 5exi + 2 20
 MTR3 2 Metrics: Query Complexity Complexity: 55 20

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_getmessage  
   reads table sybsystemprocs..sysusermessages  
   reads table master..sysmessages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..syslanguages (1)  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)  
calls proc sybsystemprocs..sp_reportstats  
   reads table master..syslogins (1)  
   writes table sybsystemprocs..sp_reportstats_rset_003 
   writes table sybsystemprocs..sp_reportstats_rset_002 
   calls proc sybsystemprocs..sp_aux_checkroleperm  
   writes table sybsystemprocs..sp_reportstats_rset_001 
read_writes table master..syslogins (1)