DatabaseProcApplicationCreatedLinks
sybsystemprocssp_optimal_text_space  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
3     /*
4     ** 18101, "Table must be in the current database."
5     ** 17230, "You must be the System Administrator (SA) or the Database Owner (dbo)
6     **         to execute this procedure."
7     ** 18102, "Table '%1!' does not exist in this database."
8     ** 19029, "The status for '%1!' is set to %2!."
9     ** 19031, "Table '%1!' does not have a text or image column."
10    ** 19032, "Due to system failure, the status for '%1!' has not been changed."
11    ** 17756, "The execution of the stored procedure '%1!' in database '%2!' was aborted because there
12    **         was an error in writing the replication log record."
13    ** 18985, "The value for '%1!' attribute must be between '%2!' and '%3!'."
14    ** 18573, "Failed to update attribute '%1!' for object '%2!'."
15    */
16    
17    create or replace procedure sp_optimal_text_space
18        @objname varchar(767), /* obj we want to mark */
19        @optname varchar(255),
20        @optvalue int
21    as
22    
23        declare @dbname varchar(255)
24        declare @db varchar(255)
25        declare @owner varchar(255)
26        declare @object varchar(255)
27        declare @objid int
28        declare @text_dealloc int
29        declare @text_keep int
30        declare @dbuid int
31        declare @dbcc_arg_3 smallint
32        declare @dbcc_arg_4 int
33        declare @nullarg char(1)
34        declare @dummy int
35        declare @status int
36        declare @gp_enabled int
37        declare @new_sysstat2 int
38    
39    
40    
41        select @text_dealloc = hextoint('0x020000000')
42        select @text_keep = hextoint('0x80000000')
43    
44        select @dbname = db_name()
45    
46        if (@optvalue not in (0, 1, 2))
47        begin
48            /*
49            ** 18985, "The value for attribute '%1!' must be
50            ** between '%2' and '%3'"
51            */
52            raiserror 18985, @optname, 0, 2
53            return (1)
54        end
55    
56        /*
57        ** Crack the name into its corresponding pieces.
58        */
59        execute sp_namecrack @objname,
60            @db = @db output,
61            @owner = @owner output,
62            @object = @object output
63    
64        /*
65        ** Make sure that the object is in the current database.
66        */
67        if (@db is not NULL and @db != db_name())
68        begin
69            /*
70            ** 18101, "Table must be in the current database."
71            */
72            raiserror 18101
73            return (1)
74        end
75        else
76        begin
77            select @dbuid = suid
78            from master..sysdatabases
79            where name = @dbname
80        end
81    
82        /* 
83        ** If granular permissions is not enabled then the user must have
84        ** sa_role or be the dbo.  If granular permissions is enabled then the
85        ** user must be the owner of the object.
86        */
87    
88        select @nullarg = NULL
89        select @objid = object_id(@objname)
90    
91        execute @status = sp_aux_checkroleperm "dbo", "@nullarg",
92            @nullarg, @gp_enabled output
93    
94        if (@gp_enabled = 0)
95        begin
96            if (@status != 0)
97            begin
98                execute @status = sp_aux_checkroleperm "sa_role", "@nullarg",
99                    @nullarg, @gp_enabled output
100               if (@status != 0)
101               begin
102                   /*
103                   ** 17230, "You must be the System Administrator (SA) or 
104                   ** the Database Owner (dbo) to execute this procedure." 
105                   */
106                   raiserror 17230
107                   return (1)
108               end
109           end
110       end
111       else
112       begin
113           if (@objid is NULL) or
114               (not exists (select name from sysobjects
115                       where id = @objid
116                           and type = "U"
117                           and uid = user_id()))
118           begin
119               /*
120               ** 17782, "You do not own a table, column or index of that name
121               ** in the current database."
122               */
123               raiserror 17782
124               return (1)
125           end
126       end
127   
128       /*
129       **  Make sure that the object actually exists.
130       */
131       if (@objid is NULL) or
132           (not exists (select name from sysobjects
133                   where id = @objid
134                       and type = "U"))
135       begin
136           /*
137           ** 18102, "Table '%1!' does not exist in this database."
138           */
139           raiserror 18102, @objname
140           return (1)
141       end
142   
143       /*
144       ** Check if the object has text/image columns in the first
145       ** place.
146       */
147       if not exists (select 1
148               from sysindexes
149               where (indid = 255) and
150                   (id = @objid))
151       begin
152           /*
153           ** 19031, "Table '%1!' does not have a text or image column."
154           */
155           raiserror 19031, @objname
156           return (1)
157       end
158   
159       /*
160       ** Save current sysstat2 value
161       */
162       select @new_sysstat2 = sysstat2 from sysobjects where id = @objid
163   
164       if (@optvalue = 1)
165       begin
166           select @dbcc_arg_3 = 1
167           select @new_sysstat2 = ((@new_sysstat2 | @text_dealloc) & ~ @text_keep)
168       end
169       else if (@optvalue = 2)
170       begin
171           select @dbcc_arg_3 = 2
172           select @new_sysstat2 = ((@new_sysstat2 | @text_keep) & ~ @text_dealloc)
173       end
174       else
175       begin
176           select @dbcc_arg_3 = 0
177           select @new_sysstat2 = (@new_sysstat2 & ~ (@text_keep | @text_dealloc))
178       end
179   
180       /*
181       ** This transaction also writes a log record for replicating the
182       ** invocation of this procedure. If logexec() fails, the transaction
183       ** is aborted.
184       **
185       ** IMPORTANT: The name rs_logexec is significant and is used by
186       ** Replication Server.
187       */
188       begin transaction rs_logexec
189   
190       update sysobjects set sysstat2 = @new_sysstat2 from sysobjects where id = @objid
191   
192       if (@@error != 0)
193       begin
194           /*
195           ** Error is already raised so just rollback.
196           */
197           rollback transaction
198   
199           return (1)
200       end
201   
202       select @dbcc_arg_4 = 0
203       dbcc chgindcachedvalue(@optname, @objname, @dbcc_arg_3, @dbcc_arg_4)
204   
205       /* If there was an error, @@error will be non-zero */
206       if (@@error != 0)
207       begin
208           rollback transaction
209   
210           /*
211           ** 18573, "Failed to update attribute '%1!' for object '%2!'."
212           */
213           raiserror 18573, @optname, @objname
214           return (1)
215       end
216   
217   
218       /*
219       ** Write the log record to replicate this invocation
220       ** of the stored procedure.
221       */
222       if (logexec() != 1)
223       begin
224           rollback transaction
225   
226           /*
227           ** 17756, "The execution of the stored procedure '%1!'
228           **         in database '%2!' was aborted because there
229           **         was an error in writing the replication log
230           **         record."
231           */
232           raiserror 17756, "sp_optimal_text_space", @dbname
233           return (1)
234       end
235   
236       commit transaction
237   
238       return (0)
239   
240   


exec sp_procxmode 'sp_optimal_text_space', 'AnyMode'
go

Grant Execute on sp_optimal_text_space to public
go
DEFECTS
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 149
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 115
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 133
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 150
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 162
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 190
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public sybsystemprocs..sp_optimal_text_space  
 MGTP 3 Grant to public sybsystemprocs..sysindexes  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MNER 3 No Error Check should check return value of exec 59
 MUCO 3 Useless Code Useless Brackets 46
 MUCO 3 Useless Code Useless Brackets 53
 MUCO 3 Useless Code Useless Brackets 67
 MUCO 3 Useless Code Useless Brackets 73
 MUCO 3 Useless Code Useless Brackets 94
 MUCO 3 Useless Code Useless Brackets 96
 MUCO 3 Useless Code Useless Brackets 100
 MUCO 3 Useless Code Useless Brackets 107
 MUCO 3 Useless Code Useless Brackets 124
 MUCO 3 Useless Code Useless Brackets 140
 MUCO 3 Useless Code Useless Brackets 156
 MUCO 3 Useless Code Useless Brackets 164
 MUCO 3 Useless Code Useless Brackets 169
 MUCO 3 Useless Code Useless Brackets 192
 MUCO 3 Useless Code Useless Brackets 199
 MUCO 3 Useless Code Useless Brackets 206
 MUCO 3 Useless Code Useless Brackets 214
 MUCO 3 Useless Code Useless Brackets 222
 MUCO 3 Useless Code Useless Brackets 233
 MUCO 3 Useless Code Useless Brackets 238
 MUUF 3 Update or Delete with Useless From Clause 190
 VNRD 3 Variable is not read @owner 61
 VNRD 3 Variable is not read @object 62
 VNRD 3 Variable is not read @dbuid 77
 VNRD 3 Variable is not read @gp_enabled 99
 VUNU 3 Variable is not used @dummy 34
 MSUB 2 Subquery Marker 114
 MSUB 2 Subquery Marker 132
 MSUB 2 Subquery Marker 147
 MTR1 2 Metrics: Comments Ratio Comments: 45% 17
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 13 = 21dec - 10exi + 2 17
 MTR3 2 Metrics: Query Complexity Complexity: 104 17

DEPENDENCIES
PROCS AND TABLES USED
reads table master..sysdatabases (1)  
calls proc sybsystemprocs..sp_namecrack  
read_writes table sybsystemprocs..sysobjects  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..sysconfigures (1)  
   reads table master..syscurconfigs (1)  
reads table sybsystemprocs..sysindexes  

CALLERS
called by proc sybsystemprocs..sp_chgattribute