| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_helpobjectdef | | 31 Aug 14 | Defects 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) |