DatabaseProcApplicationCreatedLinks
sybsystemprocssp_backup_metrics  31 Aug 14Defects 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 procedure sp_backup_metrics
18        @gid int
19    as
20        declare @uid int
21    
22        set nocount on
23    
24        /* Don't start a transaction for the user, he does not expect it. */
25        if @@trancount = 0
26        begin
27            set chained off
28        end
29    
30        /* Don't hold long READ locks, the user might not appreciate it. */
31        set transaction isolation level 1
32    
33        select @uid = nullif (user_id(), 1)
34    
35        if user_id() != 1 and charindex("sa_role", show_role()) = 0
36        begin
37            /* 17230, "You must be the System Administrator (SA) or the Database Owner (dbo) to execute this procedure." */
38            raiserror 17230
39    
40            return 1
41        end
42        else
43        begin
44            if @gid <= 0 or exists (select 1 from sysqueryplans where gid = - @gid)
45            begin
46                /* 19346, "qpmetrics group '%1!' is invalid or a group with this group ID already exists in this database." */
47                raiserror 19346, @gid
48    
49                return 2
50            end
51            else
52            begin
53                update sysqueryplans
54                set gid = - @gid
55                where
56                    gid = - 1
57            end
58        end
59    
60        return 0
61    


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}
44
 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}
56
 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 53
 MUPK 3 Update column which is part of a PK or unique index gid 54
 QISO 3 Set isolation level 31
 VNRD 3 Variable is not read @uid 33
 MSUB 2 Subquery Marker 44
 MTR1 2 Metrics: Comments Ratio Comments: 51% 17
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 4 = 5dec - 3exi + 2 17
 MTR3 2 Metrics: Query Complexity Complexity: 24 17

DEPENDENCIES
PROCS AND TABLES USED
read_writes table sybsystemprocs..sysqueryplans  

CALLERS
called by proc sybsystemprocs..sp_metrics