DatabaseProcApplicationCreatedLinks
sybsystemprocssp_drop_all_qplans  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     
4     /*
5     ** Messages for "sp_drop_all_qplans"
6     **
7     ** 17756, "The execution of the stored procedure '%1!' in database
8     **         '%2!' was aborted because there was an error in writing the
9     **         replication log record."
10    ** 18639, "There is no query plans group named '%1!' in this database."
11    */
12    
13    /*
14    ** sp_drop_all_qplans deletes all APs in a given group. 
15    ** Straightforward SYSQUERYPLANS delete by GID. 
16    ** Note, learning APs should NOT be deleted by this SP.
17    */
18    
19    create or replace procedure sp_drop_all_qplans
20        @name varchar(255)
21    as
22        declare
23            @uid int,
24            @gid int,
25            @dbname varchar(255),
26            @dummy int,
27            @nullarg char(1),
28            @gp_enabled int,
29            @status int
30    
31        set nocount on
32    
33        select @dbname = db_name()
34    
35        /* Don't start a transaction for the user, he does not expect it. */
36        if @@trancount = 0
37        begin
38            set chained off
39        end
40    
41        /* Don't hold long READ locks, the user might not appreciate it. */
42        set transaction isolation level 1
43    
44    
45        /*
46        **  If granular permissions is not enabled
47        **  only the Database Owner (DBO) or
48        **  Accounts with SA role can execute it.
49        **  If the user has SA role, it's uid will
50        **  be DBO uid (1). If granular permissions is enabled then
51        **  users with 'manage abstract plans' permission can execute it.
52        */
53    
54        select @nullarg = NULL
55    
56        execute @status = sp_aux_checkroleperm "dbo",
57            "manage abstract plans", @dbname, @gp_enabled output
58    
59        /* 
60        ** If @status = 0, user is dbo, has sa_role or if granular permissions
61        ** is enabled has 'manage abstract plans' permission.  Therefore the
62        ** user can delete all query plans in the group.
63        */
64        if (@status = 0)
65            select @uid = NULL
66        else
67            select @uid = user_id()
68    
69        exec sp_aux_get_qpgroup @name, @gid out
70    
71        if @gid is null
72        begin
73            /* 18639, "There is no query plans group named '%1!' in this database." */
74            raiserror 18639, @name
75            return 1
76        end
77    
78        /*
79        ** This transaction also writes a log record for replicating the
80        ** invocation of this procedure. If logexec() fails, the transaction
81        ** is aborted.
82        **
83        ** IMPORTANT: The name rs_logexec is significant and is used by
84        ** Replication Server.
85        */
86        begin transaction rs_logexec
87    
88        delete from sysqueryplans
89        where
90            uid = isnull(@uid, uid)
91            and gid = @gid
92            and type <= 100
93            and ((type != 10) or
94                (id not in
95                        (select id from sysqueryplans
96                        where type > 100 and type < 1000
97                        group by id)))
98    
99        /*
100       ** Write the log record to replicate this invocation
101       ** of the stored procedure.
102       */
103       if (logexec() != 1)
104       begin
105           /*
106           ** 17756, "The execution of the stored procedure
107           **         '%1!' in database '%2!' was aborted
108           **          because there was an error in writing
109           **          the replication log record."
110           */
111           raiserror 17756, "sp_drop_all_qplans", @dbname
112           rollback transaction rs_logexec
113           return (1)
114       end
115       commit transaction rs_logexec
116       return 0
117   


exec sp_procxmode 'sp_drop_all_qplans', 'AnyMode'
go

Grant Execute on sp_drop_all_qplans to public
go
DEFECTS
 QJWI 5 Join or Sarg Without Index 94
 MTYP 4 Assignment type mismatch @name: varchar(30) = varchar(255) 69
 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, type}
91
 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}
96
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 92
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 93
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 96
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 94
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 95
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 97
 MGTP 3 Grant to public sybsystemprocs..sp_drop_all_qplans  
 MGTP 3 Grant to public sybsystemprocs..sysqueryplans  
 MNER 3 No Error Check should check return value of exec 69
 MNER 3 No Error Check should check @@error after delete 88
 MUCO 3 Useless Code Useless Brackets 64
 MUCO 3 Useless Code Useless Brackets 103
 MUCO 3 Useless Code Useless Brackets 113
 QISO 3 Set isolation level 42
 VNRD 3 Variable is not read @nullarg 54
 VNRD 3 Variable is not read @gp_enabled 57
 VUNU 3 Variable is not used @dummy 26
 MSUB 2 Subquery Marker 95
 MTR1 2 Metrics: Comments Ratio Comments: 56% 19
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 4 = 5dec - 3exi + 2 19
 MTR3 2 Metrics: Query Complexity Complexity: 37 19

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