DatabaseProcApplicationCreatedLinks
sybsystemprocssp_drop_metrics  14 déc. 14Defects Propagation Dependencies

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


exec sp_procxmode 'sp_drop_metrics', 'AnyMode'
go

Grant Execute on sp_drop_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: {id}		 61
 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}		 103
 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}		 114
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 61
 MGTP 3 Grant to public sybsystemprocs..sp_drop_metrics  
 MGTP 3 Grant to public sybsystemprocs..sysqueryplans  
 MNER 3 No Error Check should check @@error after delete 58
 MNER 3 No Error Check should check @@error after delete 112
 MUCO 3 Useless Code Useless Brackets 51
 MUCO 3 Useless Code Useless Brackets 73
 MUCO 3 Useless Code Useless Brackets 75
 MUCO 3 Useless Code Useless Brackets 90
 MUCO 3 Useless Code Useless Brackets 92
 MUOT 3 Updates outside transaction 112
 QISO 3 Set isolation level 36
 VNRD 3 Variable is not read @dummy 99
 MSUB 2 Subquery Marker 103
 MTR1 2 Metrics: Comments Ratio Comments: 43% 17
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 9 = 11dec - 4exi + 2 17
 MTR3 2 Metrics: Query Complexity Complexity: 51 17

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

CALLERS
called by proc sybsystemprocs..sp_metrics