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
RESULT SETS | |
sp_help_qplan_rset_005 | |
sp_help_qplan_rset_004 | |
sp_help_qplan_rset_003 | |
sp_help_qplan_rset_002 | |
sp_help_qplan_rset_001 |
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 ![]() |