DatabaseProcApplicationCreatedLinks
sybsystemprocssp_backup_metrics  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     
4     /*
5     ** Messages for "sp_backup_metrics"
6     **
7     ** 17230, "You must be the System Administrator (SA) or the Database Owner (dbo) to execute this procedure."
8     ** 19346, "qpmetrics group '%1!' is invalid or a group with this group ID already exists in this database."
9     **
10    */
11    
12    /*
13    ** sp_backup_metrics move all qpmetrics from the default running group
14    ** to another backup group in SYSQUERYMETRICS (sa only).
15    */
16    
17    create or replace procedure sp_backup_metrics
18        @gid int
19    as
20        declare @uid int
21        declare @nullarg varchar(1)
22        declare @dummy int
23        declare @status int
24        declare @status1 int
25        declare @gp_enabled int
26        declare @dbname varchar(255)
27    
28        select @status1 = 1
29    
30        set nocount on
31    
32        /* Don't start a transaction for the user, he does not expect it. */
33        if @@trancount = 0
34        begin
35            set chained off
36        end
37    
38        /* Don't hold long READ locks, the user might not appreciate it. */
39        set transaction isolation level 1
40    
41        select @dbname = db_name()
42        /* 
43        ** If granular permissions is not enabled then the user must be dbo 
44        ** otherwise sa_role is required.
45        ** If granular permissions is enabled then the permission manage 
46        ** database is required.  proc_role and proc_auditperm will also do 
47        ** auditing if required. Both will also print error message if required.
48        */
49    
50        select @nullarg = NULL
51        execute @status = sp_aux_checkroleperm "dbo", "manage database",
52            @dbname, @gp_enabled output
53    
54        if (@status != 0)
55        begin
56            /*
57            ** Send apropriate audit records.
58            */
59            if (@gp_enabled = 0)
60            begin
61                execute @status1 = sp_aux_checkroleperm "sa_role",
62                    @nullarg, @nullarg, @gp_enabled output
63                if (@status1 > 0)
64                begin
65                    /* 
66                    ** 17230, "You must be the System Administrator
67                    **  (SA) or the Database Owner (dbo) to execute
68                    ** this procedure." 
69                    */
70                    raiserror 17230
71                    return (1)
72                end
73            end
74            else
75            begin
76                select @dummy = proc_auditperm("manage database",
77                        @status, @dbname)
78                return (1)
79            end
80        end
81    
82        /* For Auditing */
83        if (@gp_enabled = 0)
84        begin
85            if (@status1 = 0)
86            begin
87                select @dummy = proc_role("sa_role")
88            end
89        end
90        else
91        begin
92            select @dummy = proc_auditperm("manage database",
93                    @status, @dbname)
94        end
95    
96        if @gid <= 0 or exists (select 1 from sysqueryplans where gid = - @gid)
97        begin
98            /* 19346, "qpmetrics group '%1!' is invalid or a group with this group ID already exists in this database." */
99            raiserror 19346, @gid
100   
101           return 2
102       end
103       else
104       begin
105           update sysqueryplans
106           set gid = - @gid
107           where
108               gid = - 1
109       end
110   
111       return 0
112   


exec sp_procxmode 'sp_backup_metrics', 'AnyMode'
go

Grant Execute on sp_backup_metrics to public
go
DEFECTS
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysqueryplans.csysqueryplans unique clustered
(uid, gid, hashkey, id, type, sequence)
Intersection: {gid}
96
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysqueryplans.csysqueryplans unique clustered
(uid, gid, hashkey, id, type, sequence)
Intersection: {gid}
108
 MGTP 3 Grant to public sybsystemprocs..sp_backup_metrics  
 MGTP 3 Grant to public sybsystemprocs..sysqueryplans  
 MNER 3 No Error Check should check @@error after update 105
 MUCO 3 Useless Code Useless Brackets 54
 MUCO 3 Useless Code Useless Brackets 59
 MUCO 3 Useless Code Useless Brackets 63
 MUCO 3 Useless Code Useless Brackets 71
 MUCO 3 Useless Code Useless Brackets 78
 MUCO 3 Useless Code Useless Brackets 83
 MUCO 3 Useless Code Useless Brackets 85
 MUPK 3 Update column which is part of a PK or unique index gid 106
 QISO 3 Set isolation level 39
 VNRD 3 Variable is not read @dummy 92
 VUNU 3 Variable is not used @uid 20
 MSUB 2 Subquery Marker 96
 MTR1 2 Metrics: Comments Ratio Comments: 46% 17
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 7 = 9dec - 4exi + 2 17
 MTR3 2 Metrics: Query Complexity Complexity: 45 17

DEPENDENCIES
PROCS AND TABLES USED
read_writes table sybsystemprocs..sysqueryplans  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)  

CALLERS
called by proc sybsystemprocs..sp_metrics