DatabaseProcApplicationCreatedLinks
sybsystemprocssp_helpcomputedcolumn  14 déc. 14Defects Propagation 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 or replace 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
DEFECTS
 MEST 4 Empty String will be replaced by Single Space 152
 MEST 4 Empty String will be replaced by Single Space 234
 MEST 4 Empty String will be replaced by Single Space 243
 MEST 4 Empty String will be replaced by Single Space 259
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 184
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 191
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 215
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 220
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 246
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 257
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 165
 QTYP 4 Comparison type mismatch Comparison type mismatch: numeric(18,0) vs int 211
 QTYP 4 Comparison type mismatch Comparison type mismatch: numeric(18,0) vs int 230
 QTYP 4 Comparison type mismatch Comparison type mismatch: numeric(18,0) vs int 241
 QTYP 4 Comparison type mismatch Comparison type mismatch: numeric(10,0) vs int 252
 QTYP 4 Comparison type mismatch Comparison type mismatch: numeric(10,0) vs int 256
 TNOI 4 Table with no index master..syscurconfigs master..syscurconfigs
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 79
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 106
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 118
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 174
 MAW1 3 Warning message on %name% sybsystemprocs..syscomments.id: Warning message on syscomments 229
 MAW1 3 Warning message on %name% sybsystemprocs..syscomments.id: Warning message on syscomments 241
 MGTP 3 Grant to public master..syscurconfigs  
 MGTP 3 Grant to public sybsystemprocs..sp_helpcomputedcolumn  
 MGTP 3 Grant to public sybsystemprocs..syscolumns  
 MGTP 3 Grant to public sybsystemprocs..syscomments  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MNER 3 No Error Check should check @@error after insert 100
 MNER 3 No Error Check should check @@error after insert 116
 MNER 3 No Error Check should check return value of exec 132
 MNER 3 No Error Check should check return value of exec 145
 MNER 3 No Error Check should check return value of exec 149
 MNER 3 No Error Check should check return value of exec 182
 MNER 3 No Error Check should check return value of exec 184
 MNER 3 No Error Check should check return value of exec 189
 MNER 3 No Error Check should check return value of exec 191
 MNER 3 No Error Check should check @@error after insert 210
 MNER 3 No Error Check should check return value of exec 215
 MNER 3 No Error Check should check return value of exec 220
 MNER 3 No Error Check should check return value of exec 232
 MNER 3 No Error Check should check @@error after delete 235
 MNER 3 No Error Check should check @@error after insert 240
 MNER 3 No Error Check should check return value of exec 246
 MNER 3 No Error Check should check @@error after update 251
 MNER 3 No Error Check should check @@error after update 255
 MNER 3 No Error Check should check return value of exec 257
 MNER 3 No Error Check should check @@error after delete 262
 MNER 3 No Error Check should check @@error after delete 263
 MUCO 3 Useless Code Useless Brackets 62
 MUCO 3 Useless Code Useless Brackets 68
 MUCO 3 Useless Code Useless Brackets 72
 MUCO 3 Useless Code Useless Brackets 84
 MUCO 3 Useless Code Useless Brackets 103
 MUCO 3 Useless Code Useless Brackets 137
 MUCO 3 Useless Code Useless Brackets 196
 MUCO 3 Useless Code Useless Brackets 268
 MUIN 3 Column created using implicit nullability 92
 MUIN 3 Column created using implicit nullability 203
 MUIN 3 Column created using implicit nullability 204
 MUTI 3 Update temptable with identity - 12.5.4 Regression 251
 MUTI 3 Update temptable with identity - 12.5.4 Regression 255
 QAFM 3 Var Assignment from potentially many rows 163
 QISO 3 Set isolation level 44
 QIWC 3 Insert with not all columns specified missing 2 columns out of 4 116
 QNAJ 3 Not using ANSI Inner Join 227
 QNAJ 3 Not using ANSI Inner Join 240
 QNUA 3 Should use Alias: Column text should use alias syscomments 240
 QNUA 3 Should use Alias: Table #helpcpc 240
 QNUA 3 Should use Alias: Table sybsystemprocs..syscomments 240
 QNUA 3 Should use Alias: Column computedcol should use alias #helpcpc 241
 QNUA 3 Should use Alias: Column id should use alias syscomments 241
 QNUA 3 Should use Alias: Column row_id should use alias #helpcpc 241
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
106
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
118
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: syscomments.csyscomments unique clustered
(id, number, colid2, colid, texttype)
Intersection: {id}
Uncovered: [number, colid2, colid, texttype]
229
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: syscomments.csyscomments unique clustered
(id, number, colid2, colid, texttype)
Intersection: {id}
Uncovered: [number, colid2, colid, texttype]
241
 MSUB 2 Subquery Marker 78
 MSUB 2 Subquery Marker 172
 MSUB 2 Subquery Marker 227
 MTR1 2 Metrics: Comments Ratio Comments: 36% 32
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 19 = 23dec - 6exi + 2 32
 MTR3 2 Metrics: Query Complexity Complexity: 139 32

DEPENDENCIES
PROCS AND TABLES USED
reads table master..syscurconfigs (1)  
reads table sybsystemprocs..sysobjects  
read_writes table tempdb..#helptxt (1) 
calls proc sybsystemprocs..sp_autoformat  
   writes table sybsystemprocs..sp_autoformat_rset_002 
   reads table master..systypes (1)  
   writes table sybsystemprocs..sp_autoformat_rset_004 
   calls proc sybsystemprocs..sp_autoformat  
   reads table master..syscolumns (1)  
   read_writes table tempdb..#colinfo_af (1) 
   reads table tempdb..systypes (1)  
   writes table sybsystemprocs..sp_autoformat_rset_001 
   reads table tempdb..syscolumns (1)  
   writes table sybsystemprocs..sp_autoformat_rset_003 
   calls proc sybsystemprocs..sp_namecrack  
   writes table sybsystemprocs..sp_autoformat_rset_005 
reads table sybsystemprocs..syscolumns  
read_writes table tempdb..#helpcpc (1) 
writes table tempdb..#helpname (1) 
calls proc sybsystemprocs..sp_getmessage  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..syslanguages (1)  
   reads table sybsystemprocs..sysusermessages  
   reads table master..sysmessages (1)  
reads table sybsystemprocs..syscomments  

CALLERS
called by proc sybsystemprocs..sp_help  
called by proc sybsystemprocs..sp_helpindex  
   called by proc sybsystemprocs..sp_help