Database | Proc | Application | Created | Links |
sybsystemprocs | sp_drop_all_qplans | 31 Aug 14 | Defects 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 |