DatabaseProcApplicationCreatedLinks
sybsystemprocssp_drop_query_tuning  14 déc. 14Defects Propagation Dependencies

1     
2     /*
3     ** Generated by spgenmsgs.pl on Tue Dec 19 13:42:46 2006 
4     */
5     /*
6     ** raiserror Messages for drop_query_tuning [Total 3]
7     **
8     ** 17230, "You must be the System Administrator (SA) or the Database Owner (dbo) to execute this procedure."
9     ** 19627, "No query_tuning object with id = '%1!' exists in this database."
10    ** 19628, "No query_tuning group with group id = '%1!' exists in this database."
11    */
12    /*
13    ** sp_getmessage Messages for drop_query_tuning [Total 0]
14    */
15    /*
16    ** End spgenmsgs.pl output.
17    */
18    
19    /*
20    ** sp_drop_query_tuning delete records from SYSQUERYPLANS associated with
21    ** a particular query_tuning object or a query_tuning group (sa only).
22    */
23    
24    create or replace procedure sp_drop_query_tuning
25        @gid int = NULL,
26        @id int = NULL
27    as
28        declare @uid int
29    
30        declare @dummy int
31        declare @nullarg char(1)
32        declare @gp_enabled int
33        declare @status int
34        declare @dbname varchar(255)
35    
36        set nocount on
37    
38        /* Don't start a transaction for the user, he does not expect it. */
39        if @@trancount = 0
40        begin
41            set chained off
42        end
43    
44        /* Don't hold long READ locks, the user might not appreciate it. */
45        set transaction isolation level 1
46        /*
47        ** Users can access their own tables, otherwise 
48        ** if granular permissions is disabled only the Database Owner (DBO) 
49        ** or an account with sa_role can execute it. If the user has SA role, 
50        ** it's uid will be DBO uid (1). If granular permissions is enabled 
51        ** then users with 'manage abstract plans' permission can execute it.
52        */
53        select @dbname = db_name()
54        select @nullarg = NULL
55        select @uid = null
56    
57        execute @status = sp_aux_checkroleperm "sa_role",
58            "manage abstract plans", @dbname, @gp_enabled output
59        if (@status != 0)
60        begin
61            select @uid = nullif (user_id(), 1)
62        end
63    
64        if @id is not null
65        begin
66            delete from sysqueryplans
67            where
68                uid = isnull(@uid, uid)
69                and id = @id
70    
71            if @@rowcount = 0
72            begin
73                /* 19627, "No learning record with id = '%1!' exists in this database." */
74                raiserror 19627, @id
75    
76                return 1
77            end
78        end
79        else
80        begin
81            if @status != 0
82            begin
83                if @gp_enabled = 0
84                begin
85                    /* 17230, "You must be the System Administrator (SA) or the Database Owner (dbo) to execute this procedure." */
86                    raiserror 17230
87                end
88                else
89                begin
90                    select @dummy =
91                        proc_auditperm("manage abstract plans",
92                            @status, @dbname)
93                end
94    
95                return 2
96            end
97            else
98            begin
99                if @gid <= 0 or not exists (select 1 from sysqueryplans sq1 where gid = @gid and exists (select 1 from sysqueryplans sq2 where sq2.id = sq1.id and sq2.type > 100))
100               begin
101                   /* 19628, "No learning group with group id = '%1!' exists in this database." */
102                   raiserror 19628, @gid
103   
104                   return 3
105               end
106               else
107               begin
108                   delete from sysqueryplans
109                   where
110                       gid = @gid and
111                       id in (select id from sysqueryplans where type > 100 group by id)
112               end
113           end
114       end
115   
116       return 0
117   


exec sp_procxmode 'sp_drop_query_tuning', 'AnyMode'
go

Grant Execute on sp_drop_query_tuning to public
go
DEFECTS
 QJWI 5 Join or Sarg Without Index 111
 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}
69
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysqueryplans.csysqueryplans unique clustered
(uid, gid, hashkey, id, type, sequence)
Intersection: {id}
Uncovered: [uid, gid, hashkey, sequence]
99
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysqueryplans.csysqueryplans unique clustered
(uid, gid, hashkey, id, type, sequence)
Intersection: {id}
Uncovered: [uid, gid, hashkey, type, sequence]
99
 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}
99
 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}
110
 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: {type}
111
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 99
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 111
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 69
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 99
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 111
 MGTP 3 Grant to public sybsystemprocs..sp_drop_query_tuning  
 MGTP 3 Grant to public sybsystemprocs..sysqueryplans  
 MNER 3 No Error Check should check @@error after delete 66
 MNER 3 No Error Check should check @@error after delete 108
 MUCO 3 Useless Code Useless Brackets 59
 MUOT 3 Updates outside transaction 108
 QISO 3 Set isolation level 45
 VNRD 3 Variable is not read @nullarg 54
 VNRD 3 Variable is not read @dummy 90
 MSUB 2 Subquery Marker 99
 MSUB 2 Subquery Marker 111
 MSUC 2 Correlated Subquery Marker 99
 MTR1 2 Metrics: Comments Ratio Comments: 44% 24
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 9 = 11dec - 4exi + 2 24
 MTR3 2 Metrics: Query Complexity Complexity: 54 24
 PRED_QUERY_COLLECTION 2 {q=sybsystemprocs..sysqueryplans, q2=sybsystemprocs..sysqueryplans} 0 99

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_query_tuning