Database	Proc	Application	Created	Links
sybsystemprocs	sp_find_qplan		31 Aug 14	Defects Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     
4     /*
5     ** Messages for "sp_find_qplan"
6     */
7     
8     /*
9     ** sp_find_qplan does a pattern matching SYSQUERYPLANS lookup. 
10    ** @pattern is used as such, so any leading or trailing wildcards 
11    ** must be explicitly part of it. As TSQL has no TEXT append 
12    ** function, the pattern matching is done row by row. Text 
13    ** crossing row boundaries that would match the pattern is thus 
14    ** missed. Hence, all multi-row APs are signaled for manual 
15    ** checking. Once we have a decent LOB in TSQL, this should be 
16    ** revisited.
17    */
18    
19    create procedure sp_find_qplan
20        @pattern varchar(255),
21        @group varchar(30) = null
22    as
23        declare
24            @msg varchar(255),
25            @uid int,
26            @gid int
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        select @uid = nullif (user_id(), 1)
41    
42        if @group is not null
43            exec sp_aux_get_qpgroup @group, @gid out
44    
45        /* get the easy ones - where the pattern is immediately found */
46        select distinct id
47        into #found
48        from sysqueryplans
49        where
50            uid = isnull(@uid, uid)
51            and gid = isnull(@gid, gid)
52            and text like @pattern
53    
54        /* now get the ones that span over rows and didn't match yet */
55        select id
56        into #multi
57        from sysqueryplans
58        where
59            uid = isnull(@uid, uid)
60            and gid = isnull(@gid, gid)
61            and id not in (select id from #found)
62        group by gid, id
63        having count(*) > 2
64    
65        /* now look the pattern up on row boundaries */
66        insert #found
67        select distinct #multi.id
68        from
69            #multi,
70            sysqueryplans prev,
71            sysqueryplans next
72        where
73            #multi.id = prev.id
74            and #multi.id = next.id
75            and prev.type = next.type
76            and prev.sequence + 1 = next.sequence
77            and substring(prev.text, 128, 128)
78            + substring(next.text, 1, 128)
79            like @pattern
80    
81        select gid, id, text from sysqueryplans
82        where id in (select id from #found)
83        order by gid, id, type, sequence
84    
85        return 0
86    

exec sp_procxmode 'sp_find_qplan', 'AnyMode'
go

Grant Execute on sp_find_qplan to public
go

DEFECTS
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: {sequence, type} Uncovered: [uid, gid, hashkey, id]	75
QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint	76
MGTP 3 Grant to public sybsystemprocs..sp_find_qplan
MGTP 3 Grant to public sybsystemprocs..sysqueryplans
MNER 3 No Error Check should check return value of exec	43
MNER 3 No Error Check should check @@error after select into	46
MNER 3 No Error Check should check @@error after select into	55
MNER 3 No Error Check should check @@error after insert	66
QDIS 3 Check correct use of 'select distinct'	67
QGWO 3 Group by/Distinct/Union without order by	46
QGWO 3 Group by/Distinct/Union without order by	67
QISO 3 Set isolation level	37
QJWT 3 Join or Sarg Without Index on temp table	61
QJWT 3 Join or Sarg Without Index on temp table	73
QJWT 3 Join or Sarg Without Index on temp table	74
QJWT 3 Join or Sarg Without Index on temp table	82
QNAJ 3 Not using ANSI Inner Join	68
QNUA 3 Should use Alias: Table #multi	69
VUNU 3 Variable is not used @msg	24
MRST 2 Result Set Marker	81
MTR1 2 Metrics: Comments Ratio Comments: 41%	19
MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 7 = 6dec - 1exi + 2	19
MTR3 2 Metrics: Query Complexity Complexity: 40	19

DEPENDENCIES

PROCS AND TABLES USED read_writes table tempdb..#multi (1)  calls proc sybsystemprocs..sp_aux_get_qpgroup      reads table sybsystemprocs..sysattributes      calls proc sybsystemprocs..sp_aux_sargs_qpgroup   read_writes table tempdb..#found (1)  reads table sybsystemprocs..sysqueryplans