DatabaseProcApplicationCreatedLinks
sybsystemprocssp_drop_metrics  31 Aug 14Defects 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 procedure sp_drop_metrics
18        @gid int = NULL,
19        @id int = NULL
20    as
21        declare @uid int
22    
23        set nocount on
24    
25        /* Don't start a transaction for the user, he does not expect it. */
26        if @@trancount = 0
27        begin
28            set chained off
29        end
30    
31        /* Don't hold long READ locks, the user might not appreciate it. */
32        set transaction isolation level 1
33    
34        select @uid = nullif (user_id(), 1)
35    
36        if @id is not null
37        begin
38            delete from sysqueryplans
39            where
40                uid = isnull(@uid, uid)
41                and id = @id
42    
43            if @@rowcount = 0
44            begin
45                /* 19344, "No qpmetrics record with id = '%1!' exists in this database." */
46                raiserror 19344, @id
47    
48                return 1
49            end
50        end
51        else
52        begin
53            if user_id() != 1 and charindex("sa_role", show_role()) = 0
54            begin
55                /* 17230, "You must be the System Administrator (SA) or the Database Owner (dbo) to execute this procedure." */
56                raiserror 17230
57    
58                return 2
59            end
60            else
61            begin
62                if @gid <= 0 or not exists (select 1 from sysqueryplans where gid = - @gid)
63                begin
64                    /* 19345, "No qpmetrics group with group id = '%1!' exists in this database." */
65                    raiserror 19345, @gid
66    
67                    return 3
68                end
69                else
70                begin
71                    delete from sysqueryplans
72                    where
73                        gid = - @gid
74                end
75            end
76        end
77    
78        return 0
79    


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}		 41
 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}		 62
 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}		 73
 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 38
 MNER 3 No Error Check should check @@error after delete 71
 MUOT 3 Updates outside transaction 71
 QISO 3 Set isolation level 32
 MSUB 2 Subquery Marker 62
 MTR1 2 Metrics: Comments Ratio Comments: 47% 17
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 5 = 7dec - 4exi + 2 17
 MTR3 2 Metrics: Query Complexity Complexity: 34 17

DEPENDENCIES
PROCS AND TABLES USED
read_writes table sybsystemprocs..sysqueryplans  

CALLERS
called by proc sybsystemprocs..sp_metrics