| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_helpcomputedcolumn | | 31 Aug 14 | Defects Dependencies |
1 2 /* 3 ** Internal routine to be called by some external system stored procedures 4 ** to display the text of computed columns or functional index keys. 5 ** 6 ** Messages for "sp_helpcomputedcol" 7 ** 8 ** 17460, "Object must be in the current database." 9 ** 17461, "Object does not exist in this database." 10 ** 17761, "Object '%1!' is not a table." 11 ** 19334, "Warning: Configuration Parameter 'allow select on syscomments.text' 12 ** is set to 0. Only the object owner or user with sa_role can access 13 ** the text. The text for the computed column(s) cannot be 14 ** displayed." 15 ** 19335, "Warning: Configuration Parameter 'allow select on syscomments.text' 16 ** is set to 0. Only the object owner or user with sa_role can access 17 ** the text. The text for the functional index key(s) cannot be 18 ** displayed." 19 ** 19336, "Table '%1!' does not have any computed columns." 20 ** 19337, "Text is encrypted" 21 ** 19338, "Object has the following functional index keys" 22 ** 19339, "Object has the following computed columns" 23 ** 24 ** Parameters 25 ** @objname - name of the table object 26 ** @mode - Mode in which this proc is invoked: 27 ** null - invoked as a standalone proc 28 ** 0 - invoked to display computed column info 29 ** 1 - invoked to display functional index key info 30 */ 31 32 create procedure sp_helpcomputedcolumn 33 @objname varchar(767), 34 @mode int = null 35 as 36 declare @count int, @total int, @row_id int, @config_parm int 37 declare @msg varchar(1024) 38 39 if @@trancount = 0 40 begin 41 set chained off 42 end 43 44 set transaction isolation level 1 45 set nocount on 46 47 48 /* 49 ** Do necessary error checking if invoked as a standalone proc 50 */ 51 if @mode is null 52 begin 53 /* 54 ** Make sure the @objname is local to the current database. 55 */ 56 if @objname like "%.%.%" and 57 substring(@objname, 1, charindex(".", @objname) - 1) 58 != db_name() 59 begin 60 /* 17460, "Object must be in the current database." */ 61 raiserror 17460 62 return (1) 63 end 64 65 /* 66 ** See if @objname exists. 67 */ 68 if (object_id(@objname) is NULL) 69 begin 70 /* 17461, "Object does not exist in this database." */ 71 raiserror 17461 72 return (1) 73 end 74 75 /* 76 ** See if this is a table object 77 */ 78 if not exists (select 1 from sysobjects 79 where id = object_id(@objname) and 80 sysstat & 15 in (1, 3)) 81 begin 82 /* 17761, "Object '%1!' is not a table." */ 83 raiserror 17761, @objname 84 return (1) 85 end 86 end 87 88 89 /* 90 ** Create temporary table to stored computed column info 91 */ 92 create table #helpcpc(colname varchar(255), computedcol int, 93 property varchar(15) null, row_id numeric identity) 94 95 /* 96 ** Get info on computed columns 97 */ 98 if @mode != 1 99 begin 100 insert into #helpcpc(colname, computedcol, property) 101 select name, 102 computedcol, 103 case when (status2 & 32 = 32) then "materialized" 104 else "virtual" end 105 from syscolumns where 106 id = object_id(@objname) and 107 computedcol is not null and 108 status3 & 1 != 1 109 order by colid 110 end 111 /* 112 ** Get Info on functional index keys 113 */ 114 else 115 begin 116 insert into #helpcpc(colname, computedcol) 117 select name, computedcol from syscolumns where 118 id = object_id(@objname) and 119 computedcol is not null and 120 status3 & 1 = 1 121 order by colid 122 end 123 124 /* 125 ** If nothing to display 126 */ 127 if not exists (select 1 from #helpcpc) 128 begin 129 if @mode is null 130 begin 131 /* Tell the user that there is no computed column */ 132 exec sp_getmessage 19336, @msg output 133 print @msg, @objname 134 end 135 136 drop table #helpcpc 137 return (0) 138 end 139 140 /* 141 ** Display the header 142 */ 143 if @mode != 1 144 begin 145 exec sp_getmessage 19339, @msg output 146 end 147 else 148 begin 149 exec sp_getmessage 19338, @msg output 150 end 151 print @msg 152 print "" 153 154 /* 155 ** If the configuration parameter 'allow select on syscomments.text' 156 ** is set to 0, then the user can access the text ONLY in the 157 ** following cases 158 ** 159 ** 1. if the user has sa_role 160 ** 2. if the object is owned by the user 161 ** 162 */ 163 select @config_parm = value 164 from master.dbo.syscurconfigs 165 where config = 258 166 167 if @config_parm = 0 and user_id() != 1 168 begin 169 /* 170 ** The object needs to be owned by the user 171 */ 172 if not exists (select name from sysobjects 173 where uid = user_id() 174 and id = object_id(@objname)) 175 begin 176 /* 177 ** Inform the user the text cannot be displayed and 178 ** print the rest info before return 179 */ 180 if @mode != 1 181 begin 182 exec sp_getmessage 19334, @msg output 183 print @msg 184 exec sp_autoformat #helpcpc, "'Column_Name' = colname, 185 Property = property" 186 end 187 else 188 begin 189 exec sp_getmessage 19335, @msg output 190 print @msg 191 exec sp_autoformat #helpcpc, 192 "'Internal_Index_key_Name' = colname" 193 end 194 195 drop table #helpcpc 196 return (0) 197 end 198 end 199 200 /* 201 ** Now display the text 202 */ 203 create table #helptxt(text varchar(255), row_id numeric(10) identity) 204 create table #helpname(colname varchar(255), property varchar(15) null) 205 206 select @count = 1 207 select @total = max(row_id) + 1 from #helpcpc 208 while @count < @total 209 begin 210 insert into #helpname(colname, property) 211 select colname, property from #helpcpc where row_id = @count 212 213 if @mode != 1 214 begin 215 exec sp_autoformat #helpname, "'Column_Name' = colname, 216 Property = property" 217 end 218 else 219 begin 220 exec sp_autoformat #helpname, 221 "'Internal_Index_Key_Name' = colname" 222 end 223 224 /* 225 ** See if the object is hidden (SYSCOM_TEXT_HIDDEN will be set) 226 */ 227 if exists (select 1 from syscomments m, #helpcpc h where 228 (m.status & 1 = 1) and 229 m.id = h.computedcol and 230 h.row_id = @count) 231 begin 232 exec sp_getmessage 19337, @msg output 233 print @msg 234 print "" 235 delete #helpname 236 select @count = @count + 1 237 continue 238 end 239 240 insert #helptxt(text) select text from syscomments, #helpcpc 241 where row_id = @count and id = computedcol order by colid2, colid 242 243 print "" 244 if @mode != 1 245 begin 246 exec sp_autoformat #helptxt, "Text = text" 247 end 248 else 249 begin 250 select @row_id = min(row_id) from #helptxt 251 update #helptxt set text = right (text, char_length(text) - 4) 252 where row_id = @row_id 253 254 select @row_id = max(row_id) from #helptxt 255 update #helptxt set text = left (text, char_length(text) - 13) 256 where row_id = @row_id 257 exec sp_autoformat #helptxt, "Expression = text" 258 end 259 print "" 260 261 select @count = @count + 1 262 delete #helpname 263 delete #helptxt 264 end 265 266 drop table #helpcpc, #helpname, #helptxt 267 268 return (0) 269
exec sp_procxmode 'sp_helpcomputedcolumn', 'AnyMode' go Grant Execute on sp_helpcomputedcolumn to public go
| DEPENDENCIES |
| PROCS AND TABLES USED calls proc sybsystemprocs..sp_getmessage  reads table master..sysmessages (1)  calls proc sybsystemprocs..sp_validlang reads table master..syslanguages (1) reads table master..syslanguages (1) reads table sybsystemprocs..sysusermessages writes table tempdb..#helpname (1) read_writes table tempdb..#helptxt (1) read_writes table tempdb..#helpcpc (1) calls proc sybsystemprocs..sp_autoformat  reads table tempdb..systypes (1)  calls proc sybsystemprocs..sp_autoformat reads table master..syscolumns (1) reads table tempdb..syscolumns (1) calls proc sybsystemprocs..sp_namecrack reads table master..systypes (1) read_writes table tempdb..#colinfo_af (1) reads table sybsystemprocs..syscomments reads table sybsystemprocs..sysobjects reads table master..syscurconfigs (1) reads table sybsystemprocs..syscolumns CALLERS called by proc sybsystemprocs..sp_helpindex called by proc sybsystemprocs..sp_help called by proc sybsystemprocs..sp_help |
