DatabaseProcApplicationCreatedLinks
sybsystemprocssp_drop_qplan  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     
4     /*
5     ** Messages for "sp_drop_qplan"
6     **
7     ** 18636, "There is no query plan with the ID %1! in this database."
8     */
9     
10    /*
11    ** sp_drop_qplan deletes a plan form SYSQUERYPLANS. 
12    ** Straightforward delete by ID.
13    */
14    
15    create or replace procedure sp_drop_qplan
16        @id int
17    as
18        declare @uid int
19    
20        declare @dummy int
21            , @nullarg char(1)
22            , @gp_enabled int
23            , @status int
24            , @dbname varchar(255)
25    
26        select @dbname = db_name()
27    
28        set nocount on
29    
30        /* Don't start a transaction for the user, he does not expect it. */
31        if @@trancount = 0
32        begin
33            set chained off
34        end
35    
36        /* Don't hold long READ locks, the user might not appreciate it. */
37        set transaction isolation level 1
38    
39    
40        /*
41        **  Only the Database Owner (DBO) or
42        **  Accounts with SA role can execute it.
43        **  If the user has SA role, it's uid will
44        **  be DBO uid (1). If granular permissions is enabled then
45        **  users with 'manage abstract plans' permission can execute it.
46        */
47    
48        select @nullarg = NULL
49    
50        execute @status = sp_aux_checkroleperm "dbo",
51            "manage abstract plans", @dbname, @gp_enabled output
52    
53        /* 
54        ** If @status = 0, user is dbo, has sa_role or if granular permissions
55        ** is enabled has 'manage abstract plans' permission.  Therefore the
56        ** user can delete all query plans in the group.
57        */
58        if (@status = 0)
59            select @uid = NULL
60        else
61            select @uid = user_id()
62    
63        delete from sysqueryplans
64        where
65            uid = isnull(@uid, uid)
66            and id = @id
67    
68        if @@rowcount = 0
69        begin
70            /* 18636, "There is no query plan with the ID %1! in this database." */
71            raiserror 18636, @id
72    
73            return 1
74        end
75    
76        return 0
77    


exec sp_procxmode 'sp_drop_qplan', 'AnyMode'
go

Grant Execute on sp_drop_qplan 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}
66
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 66
 MGTP 3 Grant to public sybsystemprocs..sp_drop_qplan  
 MGTP 3 Grant to public sybsystemprocs..sysqueryplans  
 MNER 3 No Error Check should check @@error after delete 63
 MUCO 3 Useless Code Useless Brackets 58
 QISO 3 Set isolation level 37
 VNRD 3 Variable is not read @nullarg 48
 VNRD 3 Variable is not read @gp_enabled 51
 VUNU 3 Variable is not used @dummy 20
 MTR1 2 Metrics: Comments Ratio Comments: 50% 15
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 4 = 4dec - 2exi + 2 15
 MTR3 2 Metrics: Query Complexity Complexity: 24 15

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