DatabaseProcApplicationCreatedLinks
sybsystemprocssp_drop_all_qplans  31 Aug 14Defects 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 procedure sp_drop_all_qplans
20        @name varchar(255)
21    as
22        declare
23            @uid int,
24            @gid int,
25            @dbname char(255)
26    
27        set nocount on
28    
29        select @dbname = db_name()
30    
31        /* Don't start a transaction for the user, he does not expect it. */
32        if @@trancount = 0
33        begin
34            set chained off
35        end
36    
37        /* Don't hold long READ locks, the user might not appreciate it. */
38        set transaction isolation level 1
39    
40    
41        select @uid = nullif (user_id(), 1)
42    
43        exec sp_aux_get_qpgroup @name, @gid out
44    
45        if @gid is null
46        begin
47            /* 18639, "There is no query plans group named '%1!' in this database." */
48            raiserror 18639, @name
49            return 1
50        end
51    
52        /*
53        ** This transaction also writes a log record for replicating the
54        ** invocation of this procedure. If logexec() fails, the transaction
55        ** is aborted.
56        **
57        ** IMPORTANT: The name rs_logexec is significant and is used by
58        ** Replication Server.
59        */
60        begin transaction rs_logexec
61    
62        delete from sysqueryplans
63        where
64            uid = isnull(@uid, uid)
65            and gid = @gid
66            and type <= 100
67            and ((type != 10) or
68                (id not in
69                        (select id from sysqueryplans
70                        where type > 100 and type < 1000
71                        group by id)))
72    
73        /*
74        ** Write the log record to replicate this invocation
75        ** of the stored procedure.
76        */
77        if (logexec() != 1)
78        begin
79            /*
80            ** 17756, "The execution of the stored procedure
81            **         '%1!' in database '%2!' was aborted
82            **          because there was an error in writing
83            **          the replication log record."
84            */
85            raiserror 17756, "sp_drop_all_qplans", @dbname
86            rollback transaction rs_logexec
87            return (1)
88        end
89        commit transaction rs_logexec
90        return 0
91    


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 68
 MTYP 4 Assignment type mismatch @name: varchar(30) = varchar(255) 43
 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}
65
 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}
70
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 66
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 67
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 70
 MGTP 3 Grant to public sybsystemprocs..sp_drop_all_qplans  
 MGTP 3 Grant to public sybsystemprocs..sysqueryplans  
 MLCH 3 Char type with length>30 char(255) 25
 MNER 3 No Error Check should check return value of exec 43
 MNER 3 No Error Check should check @@error after delete 62
 MUCO 3 Useless Code Useless Brackets 77
 MUCO 3 Useless Code Useless Brackets 87
 QISO 3 Set isolation level 38
 MSUB 2 Subquery Marker 69
 MTR1 2 Metrics: Comments Ratio Comments: 54% 19
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 2 = 3dec - 3exi + 2 19
 MTR3 2 Metrics: Query Complexity Complexity: 32 19

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_aux_get_qpgroup  
   reads table sybsystemprocs..sysattributes  
   calls proc sybsystemprocs..sp_aux_sargs_qpgroup  
read_writes table sybsystemprocs..sysqueryplans