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

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     
4     /*
5     ** Messages for "sp_help_qplan"	
6     **
7     ** 18640, "Unknown %1! option : '%2!'. Valid options are : %3!."
8     */
9     
10    /*
11    ** sp_help_qplan displays a plan. The display modes can be "brief", 
12    ** "list" and "full". The default display mode is "brief".
13    */
14    
15    create procedure sp_help_qplan
16        @id int,
17        @mode varchar(20) = "brief"
18    as
19        declare @uid int
20        declare @retval int
21    
22        set nocount on
23    
24        /* Don't start a transaction for the user, he does not expect it. */
25        if @@trancount = 0
26        begin
27            set chained off
28        end
29    
30        /* Don't hold long READ locks, the user might not appreciate it. */
31        set transaction isolation level 1
32    
33    
34        select @uid = nullif (user_id(), 1)
35    
36        if @mode = "list"
37        begin
38            select hashkey, id,
39                substring(text, 1, 20) + "..." as "query",
40                    (select substring(text, 1, 20) + "..."
41                    from sysqueryplans
42                    where uid = isnull(@uid, uid)
43                        and id = @id
44                        and type = 100
45                        and sequence = 0) as "query_plan"
46            from sysqueryplans
47            where uid = isnull(@uid, uid)
48                and id = @id
49                and type = 10
50                and sequence = 0
51    
52            return 0
53        end
54    
55        if @mode = "brief"
56        begin
57            select gid, hashkey, id
58            from sysqueryplans
59            where uid = isnull(@uid, uid)
60                and id = @id
61                and type = 10
62                and sequence = 0
63    
64            select case
65                    when char_length(text) <= 78
66                    then substring(text, 1, 78)
67                    else substring(text, 1, 75) + "..."
68                end as "query"
69            from sysqueryplans
70            where uid = isnull(@uid, uid)
71                and id = @id
72                and type = 10
73                and sequence = 0
74    
75            select case
76                    when char_length(text) <= 78
77                    then substring(text, 1, 78)
78                    else substring(text, 1, 75) + "..."
79                end as "query_plan"
80            from sysqueryplans
81            where uid = isnull(@uid, uid)
82                and id = @id
83                and type = 100
84                and sequence = 0
85    
86            return 0
87        end
88    
89        if @mode = "full"
90        begin
91            select gid, hashkey, id
92            from sysqueryplans
93            where uid = isnull(@uid, uid)
94                and id = @id
95                and type = 10
96                and sequence = 0
97    
98            select text as "query" into #query_text
99            from sysqueryplans
100           where uid = isnull(@uid, uid)
101               and id = @id
102               and type = 10
103           order by sequence
104   
105           exec @retval = sp_autoformat #query_text
106           drop table #query_text
107           if (@retval != 0)
108               return 1
109   
110           select text as "query_plan" into #plan_text
111           from sysqueryplans
112           where uid = isnull(@uid, uid)
113               and id = @id
114               and type = 100
115           order by sequence
116   
117           exec @retval = sp_autoformat #plan_text
118           drop table #plan_text
119           if (@retval != 0)
120               return 1
121   
122           return 0
123       end
124   
125       /* 18640, "Unknown %1! option : '%2!'. Valid options are : %3!." */
126       raiserror 18640, "sp_help_qplan", @mode, "'brief', 'list', 'full'"
127   
128       return 1
129   

exec sp_procxmode 'sp_help_qplan', 'AnyMode'
go

Grant Execute on sp_help_qplan to public
go

DEFECTS
MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1	105
MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1	117
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, id}	101
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, id}	113
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	44
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	45
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	49
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	50
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	61
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	62
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	72
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	73
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	83
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	84
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	95
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	96
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	102
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	114
MGTP 3 Grant to public sybsystemprocs..sp_help_qplan
MGTP 3 Grant to public sybsystemprocs..sysqueryplans
MNER 3 No Error Check should check @@error after select into	98
MNER 3 No Error Check should check return value of exec	105
MNER 3 No Error Check should check @@error after select into	110
MNER 3 No Error Check should check return value of exec	117
MUCO 3 Useless Code Useless Brackets	107
MUCO 3 Useless Code Useless Brackets	119
QCRS 3 Conditional Result Set	38
QCRS 3 Conditional Result Set	57
QCRS 3 Conditional Result Set	64
QCRS 3 Conditional Result Set	75
QCRS 3 Conditional Result Set	91
QCTC 3 Conditional Table Creation	98
QCTC 3 Conditional Table Creation	110
QISO 3 Set isolation level	31
MRST 2 Result Set Marker	38
MRST 2 Result Set Marker	57
MRST 2 Result Set Marker	64
MRST 2 Result Set Marker	75
MRST 2 Result Set Marker	91
MSUB 2 Subquery Marker	40
MTR1 2 Metrics: Comments Ratio Comments: 16%	15
MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 6 = 10dec - 6exi + 2	15
MTR3 2 Metrics: Query Complexity Complexity: 79	15

DEPENDENCIES

PROCS AND TABLES USED reads table sybsystemprocs..sysqueryplans   writes table tempdb..#plan_text (1)  calls proc sybsystemprocs..sp_autoformat      reads table tempdb..systypes (1)      reads table tempdb..syscolumns (1)      reads table master..systypes (1)      calls proc sybsystemprocs..sp_autoformat      read_writes table tempdb..#colinfo_af (1)     reads table master..syscolumns (1)      calls proc sybsystemprocs..sp_namecrack   writes table tempdb..#query_text (1)  CALLERS called by proc sybsystemprocs..sp_cmp_all_qplans