DatabaseProcApplicationCreatedLinks
sybsystemprocssp_helpobjectdef  31 Aug 14Defects Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     
4     /*
5     ** Omni only
6     **
7     ** Messages for "sp_helpobjectdef"
8     **
9     ** 17240, "'%1!' is not a valid name."
10    ** 17461, "Object does not exist in this database." 
11    ** 18300, "A server name is not permitted in the local object_name." 
12    ** 18301, "Database name '%1!' is not your current database."
13    ** 18302, "User '%1!' is not a valid user in the '%2!' database."
14    */
15    create procedure sp_helpobjectdef
16        @objname varchar(1023) = NULL /* object name we're after */
17    as
18    
19        declare @retcode int,
20            @len1 int,
21            @len2 int
22        declare @dso_class smallint,
23            @attrib smallint,
24            @uid int,
25            @maxlen int
26        declare @server varchar(255),
27            @dbname varchar(255),
28            @owner varchar(255),
29            @object varchar(255)
30    
31        if @@trancount = 0
32        begin
33            set chained off
34        end
35    
36        set transaction isolation level 1
37    
38        set nocount on
39    
40        /*
41        ** Search sysattributes using the DSO class 
42        ** and Object Definition attribute.
43        */
44        select @dso_class = 9, @attrib = 1
45    
46        /*
47        **  If no object name is supplied, give info about all Object Definitions
48        */
49        if @objname is NULL
50        begin
51            select Owner = user_name(a.object_info1),
52                Object = a.object_cinfo,
53                Type = v.name,
54                Definition = a.char_value,
55                case
56                    when a.int_value & 1 > 0 then 'implicit'
57                    else 'explicit'
58                end as Usage,
59                case
60                    when a.int_value & 4 > 0 then 'non transactional'
61                    when a.int_value & 2 > 0 then 'transactional'
62                    when a.object_info2 = 3 then 'transactional'
63                    else NULL
64                end as Status
65            into #sphelpobjectdef1rs
66            from sysattributes a, master.dbo.spt_values v
67            where a.class = @dso_class and a.attribute = @attrib
68                and a.object_info2 = v.number and v.type = 'Y'
69                and v.name != 'view'
70            exec sp_autoformat @fulltabname = #sphelpobjectdef1rs,
71                @orderby = "order by Owner, Object"
72            drop table #sphelpobjectdef1rs
73    
74            return (0)
75        end
76    
77        /*
78        ** Check that @objname refers to object in current database,
79        ** and determine correct user id.
80        */
81        exec @retcode = sp_namecrack @objname, @server output, @dbname output,
82            @owner output, @object output
83        if @retcode != 0
84        begin
85            /*
86            ** 17240, "'%1!' is not a valid name."
87            */
88            raiserror 17240, @objname
89            return (1)
90    
91        end
92    
93        /*
94        ** Make sure that a server name wasn't supplied
95        */
96        if @server is not null
97        begin
98            /*
99            ** 18300, "A server name is not permitted in the local object_name." 
100           */
101           raiserror 18300
102           return (1)
103       end
104   
105       /*
106       ** If a database name was supplied, it must be for the current database
107       */
108       if @dbname is not null
109       begin
110           if @dbname != db_name()
111           begin
112               /*
113               ** 18301, "Database name '%1!' is not your current database."
114               */
115               raiserror 18301, @dbname
116               return (1)
117           end
118       end
119   
120       /*
121       ** If an owner name was supplied, validate it as well
122       */
123       if @owner is NULL
124       begin
125           select @uid = user_id()
126       end
127       else
128       begin
129           select @uid = user_id(@owner)
130           if @uid is NULL
131           begin
132               select @dbname = db_name()
133               /*
134               ** 18302, "User '%1!' is not a valid user in the '%2!' database."
135               */
136               raiserror 18302, @owner, @dbname
137               return (1)
138           end
139       end
140   
141       /*
142       ** Check that the local object name is valid
143       */
144       select @maxlen = length from syscolumns
145       where id = object_id("sysobjects") and name = "name"
146   
147       if valid_name(@object, @maxlen) = 0
148       begin
149           /*
150           ** 17240, "'%1!' is not a valid name."
151           */
152           raiserror 17240, @object
153           return (1)
154       end
155   
156       /*
157       ** Strip out quotes from table name if quoted identifier is
158       ** on
159       */
160       if @object like '"%"'
161       begin
162           select @object = substring(@object, 2, char_length(@object) - 2)
163       end
164   
165       /*
166       **  Now check to see if the object is in sysattributes
167       */
168       if not exists (select *
169               from sysattributes
170               where class = @dso_class and attribute = @attrib and
171                   object_cinfo = @object and
172                   (object_info1 = @uid or @owner is NULL))
173   
174       begin
175           /* 
176           ** 17461, "Object does not exist in this database." 
177           */
178           raiserror 17461
179           return (1)
180       end
181   
182       /*
183       ** The object is in sysattributes, so display the information.
184       */
185       begin
186           if @owner is NULL
187           begin
188               select Owner = user_name(a.object_info1),
189                   Object = a.object_cinfo,
190                   Type = v.name,
191                   Definition = a.char_value,
192                   case
193                       when a.int_value & 1 > 0 then 'implicit'
194                       else 'explicit'
195                   end as Usage,
196                   case
197                       when a.int_value & 4 > 0 then 'non transactional'
198                       when a.int_value & 2 > 0 then 'transactional'
199                       when a.object_info2 = 3 then 'transactional'
200                       else NULL
201                   end as Status
202               into #sphelpobjectdef2rs
203               from sysattributes a, master.dbo.spt_values v
204               where a.class = @dso_class and a.attribute = @attrib
205                   and a.object_info2 = v.number and v.type = 'Y'
206                   and v.name != 'view'
207                   and a.object_cinfo = @object
208               exec sp_autoformat @fulltabname = #sphelpobjectdef2rs,
209                   @orderby = "order by Owner"
210               drop table #sphelpobjectdef2rs
211           end
212           else
213           begin
214               select Owner = user_name(a.object_info1),
215                   Object = a.object_cinfo,
216                   Type = v.name,
217                   Definition = a.char_value,
218                   case
219                       when a.int_value & 1 > 0 then 'implicit'
220                       else 'explicit'
221                   end as Usage,
222                   case
223                       when a.int_value & 4 > 0 then 'non transactional'
224                       when a.int_value & 2 > 0 then 'transactional'
225                       when a.object_info2 = 3 then 'transactional'
226                       else NULL
227                   end as Status
228               into #sphelpobjectdef3rs
229               from sysattributes a, master.dbo.spt_values v
230               where a.class = @dso_class and a.attribute = @attrib
231                   and a.object_info2 = v.number and v.type = 'Y'
232                   and v.name != 'view'
233                   and a.object_cinfo = @object and a.object_info1 = @uid
234               exec sp_autoformat @fulltabname = #sphelpobjectdef3rs
235               drop table #sphelpobjectdef3rs
236           end
237       end
238   
239       return (0)
240   
241   


exec sp_procxmode 'sp_helpobjectdef', 'AnyMode'
go

Grant Execute on sp_helpobjectdef to public
go
DEFECTS
 MINU 4 Unique Index with nullable columns sybsystemprocs..sysattributes sybsystemprocs..sysattributes
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 70
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 208
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 234
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_info2}
Uncovered: [object_type, object, object_info1, object_info3, object_cinfo]
68
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_info2}
Uncovered: [object_type, object, object_info1, object_info3]
205
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_info2}
Uncovered: [object_type, object, object_info3]
231
 TNOU 4 Table with no unique index master..spt_values master..spt_values
 MGTP 3 Grant to public master..spt_values  
 MGTP 3 Grant to public sybsystemprocs..sp_helpobjectdef  
 MGTP 3 Grant to public sybsystemprocs..sysattributes  
 MGTP 3 Grant to public sybsystemprocs..syscolumns  
 MNER 3 No Error Check should check @@error after select into 51
 MNER 3 No Error Check should check return value of exec 70
 MNER 3 No Error Check should check @@error after select into 188
 MNER 3 No Error Check should check return value of exec 208
 MNER 3 No Error Check should check @@error after select into 214
 MNER 3 No Error Check should check return value of exec 234
 MUCO 3 Useless Code Useless Brackets 74
 MUCO 3 Useless Code Useless Brackets 89
 MUCO 3 Useless Code Useless Brackets 102
 MUCO 3 Useless Code Useless Brackets 116
 MUCO 3 Useless Code Useless Brackets 137
 MUCO 3 Useless Code Useless Brackets 153
 MUCO 3 Useless Code Useless Brackets 179
 MUCO 3 Useless Code Useless Begin-End Pair 185
 MUCO 3 Useless Code Useless Brackets 239
 QAFM 3 Var Assignment from potentially many rows 144
 QCTC 3 Conditional Table Creation 51
 QCTC 3 Conditional Table Creation 188
 QCTC 3 Conditional Table Creation 214
 QISO 3 Set isolation level 36
 QNAJ 3 Not using ANSI Inner Join 66
 QNAJ 3 Not using ANSI Inner Join 203
 QNAJ 3 Not using ANSI Inner Join 229
 QPNC 3 No column in condition 172
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
145
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_info1, object_cinfo, attribute, class}
170
 VUNU 3 Variable is not used @len1 20
 VUNU 3 Variable is not used @len2 21
 MSUB 2 Subquery Marker 168
 MTR1 2 Metrics: Comments Ratio Comments: 26% 15
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 26 = 32dec - 8exi + 2 15
 MTR3 2 Metrics: Query Complexity Complexity: 109 15
 PRED_QUERY_COLLECTION 2 {a=sybsystemprocs..sysattributes, sv=master..spt_values} 0 51
 PRED_QUERY_COLLECTION 2 {a=sybsystemprocs..sysattributes, sv=master..spt_values} 0 188
 PRED_QUERY_COLLECTION 2 {a=sybsystemprocs..sysattributes, sv=master..spt_values} 0 214

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..sysattributes  
reads table sybsystemprocs..syscolumns  
calls proc sybsystemprocs..sp_namecrack  
calls proc sybsystemprocs..sp_autoformat  
   reads table tempdb..syscolumns (1)  
   read_writes table tempdb..#colinfo_af (1) 
   calls proc sybsystemprocs..sp_autoformat  
   reads table master..syscolumns (1)  
   reads table tempdb..systypes (1)  
   reads table master..systypes (1)  
   calls proc sybsystemprocs..sp_namecrack  
reads table master..spt_values (1)  
writes table tempdb..#sphelpobjectdef1rs (1) 
writes table tempdb..#sphelpobjectdef3rs (1) 
writes table tempdb..#sphelpobjectdef2rs (1)