DatabaseProcApplicationCreatedLinks
sybsystemprocssp_help_params  14 déc. 14Defects Propagation Dependencies

1     
2     /*
3     ** sp_help_params
4     **
5     **	Servant sproc to sp_help to pull out column related information
6     **	for a procedure, trigger, or SQL-J functions. It is assumed that the
7     ** 	caller has validated tha the object exists, and is one of these kinds.
8     **	If not, you will just get no rows back, which will still be ok.
9     ** 	(Currently, this is just an internal proc. If it gets to be used as
10    **	user-visible proc, we need updates to check for @objname etc.)
11    **
12    ** Parameters:
13    **	@objname	- Name of object, possibly multi-part
14    **
15    ** Returns:	Nothing.
16    {
17    */
18    create or replace procedure sp_help_params(@objname varchar(767)
19    ) as
20        begin
21            declare @sptlang int
22                , @grp_count int
23                , @selectlist_str varchar(80)
24                , @orderby_str varchar(40)
25    
26            -- Does the sproc have any parameters? It not, nothing to do here.
27            if not exists (select 1
28                    from syscolumns c, systypes t
29                    where c.id = object_id(@objname)
30                        and c.usertype *= t.usertype)
31                return 0
32    
33            select @sptlang = @@langid
34    
35            if (@sptlang != 0) -- User has defined a non-default language
36            begin
37                if not exists (select * from master.dbo.sysmessages
38                        where error between 17100 and 17109
39                            and langid = @@langid)
40                    select @sptlang = 0
41            end
42    
43            set nocount on
44    
45        end
46        -- Parameter_name _can_ be NULL for SQL-J functions, as per
47        -- the SQL-J spec, but currently we don't allow that. Even 
48        -- if we do so in the future, the name will come out as
49        -- NULL, which is ok. Now, we do convert() to varchar() NULL
50        -- as this is used to create a new table, and inserting NULL
51        -- name will cause a problem at run-time.
52        --
53        select Parameter_name = convert(varchar(255) NULL, c.name),
54            Type = isnull(convert(char(30), x.xtname),
55                isnull(convert(char(30),
56                    get_xtypename(c.xtype, c.xdbid)),
57                    t.name)),
58            Length = c.length,
59            Prec = c.prec,
60            Scale = c.scale,
61            Param_order = c.colid,
62            Grp_number = c.number,
63            rtype = t.type,
64            utype = t.usertype,
65    
66            /*
67            ** Handle the case where upgraded databases
68            ** have syscolumns.status2 as NULL. Report
69            ** those as 'unknown' parameter types.
70            */
71            Mode = case c.status2
72                when NULL then "unknown"
73                else (select convert(nvarchar(20), mi.description)
74                        from master.dbo.spt_values vi
75                        , master.dbo.sysmessages mi
76                        where vi.number = c.status2
77                            and vi.msgnum = mi.error
78                            and isnull(mi.langid, 0) = @sptlang
79                            and mi.error between 17583 and 17586)
80            end
81        into #helpproc
82        from syscolumns c, systypes t, sysxtypes x
83        where c.id = object_id(@objname)
84            and c.usertype *= t.usertype
85            and c.xtype *= x.xtid
86    
87        /*  If it is a procedure group, grp_count>1 */
88        select @grp_count = count(distinct Grp_number)
89        from #helpproc
90    
91        /* Handle National Characters */
92        update #helpproc
93        set Length = Length / @@ncharsize
94        where (rtype = 47 and utype = 24)
95            or (rtype = 39 and utype = 25)
96    
97        /* Handle unichar/univarchar */
98        update #helpproc
99        set Length = Length / @@unicharsize
100       where rtype in (select type from systypes
101               where name in ('unichar', 'univarchar'))
102   
103       /* Handle unsigned types by outputing user syntax */
104       update #helpproc
105       set Type = "unsigned " +
106           substring(Type, charindex("u", Type) + 1, 30)
107       where utype in (44, 45, 46)
108   
109       /*  For display, group by group number too in case
110       **  it is a procedure group
111       */
112       if (@grp_count = 1)
113       begin
114           select @selectlist_str = "Parameter_name,Type,Length,Prec,Scale,Param_order,Mode"
115               , @orderby_str = "order by Param_order asc"
116       end
117       else
118       begin
119           select @selectlist_str = "Parameter_name,Type,Length,Prec,Scale,Param_order,Mode,Grp_number"
120               , @orderby_str = "order by Grp_number asc, Param_order asc"
121       end
122   
123       exec sp_autoformat @fulltabname = #helpproc,
124           @selectlist = @selectlist_str,
125           @orderby = @orderby_str
126       drop table #helpproc
127   


exec sp_procxmode 'sp_help_params', 'AnyMode'
go

Grant Execute on sp_help_params to public
go
DEFECTS
 QJWI 5 Join or Sarg Without Index 30
 QJWI 5 Join or Sarg Without Index 84
 QJWI 5 Join or Sarg Without Index 85
 MINU 4 Unique Index with nullable columns master..sysmessages master..sysmessages
 MINU 4 Unique Index with nullable columns sybsystemprocs..sysxtypes sybsystemprocs..sysxtypes
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 123
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 39
 QTYP 4 Comparison type mismatch smallint = int 39
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 94
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 94
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 95
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 95
 TNOU 4 Table with no unique index master..spt_values master..spt_values
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 29
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 83
 MGTP 3 Grant to public master..spt_values  
 MGTP 3 Grant to public master..sysmessages  
 MGTP 3 Grant to public sybsystemprocs..sp_help_params  
 MGTP 3 Grant to public sybsystemprocs..syscolumns  
 MGTP 3 Grant to public sybsystemprocs..systypes  
 MGTP 3 Grant to public sybsystemprocs..sysxtypes  
 MNER 3 No Error Check should check @@error after select into 53
 MNER 3 No Error Check should check @@error after update 92
 MNER 3 No Error Check should check @@error after update 98
 MNER 3 No Error Check should check @@error after update 104
 MNER 3 No Error Check should check return value of exec 123
 MUCO 3 Useless Code Useless Brackets in create proc 18
 MUCO 3 Useless Code Useless Begin-End Pair 20
 MUCO 3 Useless Code Useless Brackets 35
 MUCO 3 Useless Code Useless Brackets 112
 QJWT 3 Join or Sarg Without Index on temp table 100
 QNAJ 3 Not using ANSI Inner Join 74
 QNAO 3 Not using ANSI Outer Join 28
 QNAO 3 Not using ANSI Outer Join 82
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
29
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid}
38
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {number}
Uncovered: [type]
76
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error}
Uncovered: [dlevel]
77
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
83
 QTJ1 3 Table only appears in inner join clause 28
 QTLO 3 Top-Level OR 94
 MSUB 2 Subquery Marker 27
 MSUB 2 Subquery Marker 37
 MSUB 2 Subquery Marker 100
 MSUC 2 Correlated Subquery Marker 73
 MTR1 2 Metrics: Comments Ratio Comments: 35% 18
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 15 = 14dec - 1exi + 2 18
 MTR3 2 Metrics: Query Complexity Complexity: 55 18
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, m=master..sysmessages, sv=master..spt_values} 0 73

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..systypes  
calls proc sybsystemprocs..sp_autoformat  
   writes table sybsystemprocs..sp_autoformat_rset_002 
   reads table tempdb..systypes (1)  
   writes table sybsystemprocs..sp_autoformat_rset_001 
   reads table master..systypes (1)  
   calls proc sybsystemprocs..sp_autoformat  
   writes table sybsystemprocs..sp_autoformat_rset_003 
   writes table sybsystemprocs..sp_autoformat_rset_005 
   reads table tempdb..syscolumns (1)  
   reads table master..syscolumns (1)  
   writes table sybsystemprocs..sp_autoformat_rset_004 
   calls proc sybsystemprocs..sp_namecrack  
   read_writes table tempdb..#colinfo_af (1) 
reads table master..sysmessages (1)  
reads table sybsystemprocs..sysxtypes  
read_writes table tempdb..#helpproc (1) 
reads table sybsystemprocs..syscolumns  
reads table master..spt_values (1)  

CALLERS
called by proc sybsystemprocs..sp_showtext_sproc_tagline  
   called by proc sybsystemprocs..sp_showtext_output  
      called by proc sybsystemprocs..sp_showtext  
         called by proc sybsystemprocs..sp_helptext_usage  
            called by proc sybsystemprocs..sp_helptext  
         called by proc sybsystemprocs..sp_helptext  
called by proc sybsystemprocs..sp_help