DatabaseProcApplicationCreatedLinks
sybsystemprocssp_unbindefault  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
3     /*	4.8	1.1	06/14/90	sproc/src/serveroption */
4     
5     /*
6     ** Messages for "sp_unbindefault"       17840
7     ** 17756, "The execution of the stored procedure '%1!' in database
8     **         '%2!' was aborted because there was an error in writing the
9     **         replication log record."
10    ** 17763, "The execution of the stored procedure '%1!' in database '%2!' was aborted because there
11    **         was an error in updating the schemacnt column in sysobjects."
12    ** 17840, "Column or usertype must be in 'current' database."
13    ** 17841, "Default unbound from table column."
14    ** 17842, "The specified column has no default."
15    ** 17843, "You do not own a table with a column of that name."
16    ** 17844, "You do not own a user datatype of that name."
17    ** 17845, "The specified user datatype has no default."
18    ** 17846, "Default unbound from datatype."
19    ** 17847, "Columns of the user datatype specified had their defaults unbound."
20    ** 17848, "You cannot unbind a declared default. Use ALTER TABLE command."
21    ** 18293, "Auditing for '%1!' event has failed due to internal error. Contact a user with System Security Officer (SSO) role."
22    */
23    
24    create or replace procedure sp_unbindefault
25        @objname varchar(511), /* table/column or datatype name */
26        @futureonly varchar(15) = NULL /* flag to indicate extent of binding */
27    as
28    
29        declare @defid int /* id of the default to unbind */
30        declare @futurevalue varchar(15) /* the value of @futureonly that causes
31        ** the binding to be limited */
32        declare @msg varchar(1024)
33        declare @returncode int /* return from ad_hoc_audit builtin */
34        declare @eventnum int /* event number for bind default auditing */
35        declare @mod_ok int /* successful bind default auditing  */
36        declare @mod_fail int /* failure bind default auditing  */
37    
38    
39        select @eventnum = 67 /* unbind default event number */
40        select @mod_ok = 1
41        select @mod_fail = 2
42        declare @dbname varchar(255)
43        declare @tmp int
44        declare @rows_selected int
45    
46    
47    
48        if @@trancount = 0
49        begin
50            set chained off
51        end
52    
53        set transaction isolation level 1
54    
55        set transaction isolation level 1
56    
57        select @futurevalue = "futureonly" /* initialize @futurevalue */
58    
59        /*
60        **  When a default or rule is bound to a user-defined datatype, it is also
61        **  bound, by default, to any columns of the user datatype that are currently
62        **  using the existing default or rule as their default or rule.  This default
63        **  action may be overridden by setting @futureonly = @futurevalue when the
64        **  procedure is invoked.  In this case existing columns with the user
65        **  datatype won't have their existing default or rule changed.
66        */
67    
68        /*
69        **  Check to see that the object names are local to the current database.
70        */
71        if @objname like "%.%.%"
72        begin
73            /*
74            ** 17840, "Column or usertype must be in 'current' database."
75            */
76            raiserror 17840
77            return (1)
78        end
79    
80        /*
81        **  If @objname is of the form tab.col then we are unbinding a column.
82        **  Otherwise its a datatype.  In the column case, we need to extract
83        **  and verify the table and column names and make sure the user owns
84        **  the table that is getting the default unbound.
85        */
86        if @objname like "%.%"
87        begin
88            declare @tabname varchar(255) /* name of table */
89            declare @colname varchar(255) /* name of column */
90    
91            /*
92            **  Get the table name out.
93            */
94            select @tabname = substring(@objname, 1, charindex(".", @objname) - 1)
95            select @colname = substring(@objname, charindex(".", @objname) + 1, 511)
96    
97            /*
98            **  Find it.
99            */
100           select @defid = syscolumns.cdefault
101           from syscolumns, sysobjects
102           where syscolumns.id = object_id(@tabname)
103               and syscolumns.name = @colname
104               and sysobjects.id = object_id(@tabname)
105               and uid = user_id()
106               and sysobjects.sysstat & 7 = 3 /* user table */
107   
108           if @defid is NULL
109           begin
110               /*
111               **  17843, "You do not own a table with a column of that name."
112               */
113   
114               /* Audit the failure to unbind a default */
115               select @returncode =
116                   ad_hoc_audit(@eventnum, @mod_fail, NULL, db_name(),
117                       @tabname, user_name(), 0, object_id(@tabname)
118   
119                   )
120               raiserror 17843
121               return (1)
122           end
123   
124   
125   
126           /* Audit the succssful permission to unbind a default */
127           select @returncode =
128               ad_hoc_audit(@eventnum, @mod_ok, NULL, db_name(),
129                   @tabname, user_name(), 0, object_id(@tabname)
130   
131               )
132           if (@returncode != 0)
133           begin
134               /* 
135               ** 18293, "Auditing for '%1!' event has failed due to 
136               ** internal error. Contact a user with System Security 
137               ** Officer (SSO) role."
138               */
139               raiserror 18293, @eventnum
140               return (1)
141           end
142           /*
143           **  Is there something bound to it?
144           */
145           if @defid = 0
146           begin
147               /*
148               ** 17842, "The specified column has no default."
149               */
150               raiserror 17842
151               return (1)
152           end
153           /*
154           ** Check to see that the default is not of declared type
155           */
156   
157           if (exists (select * from sysprocedures
158                       where id = @defid
159                           and sequence = 0
160                           and status & 4096 = 4096)
161               ) or (exists (select * from syscomments
162                       where id = @defid
163                           and status & 8 = 8) -- sharable inline default
164               )
165           begin
166               /*
167               ** 17848, "You cannot unbind a declared default. Use ALTER TABLE command."
168               */
169               raiserror 17848
170               return (1)
171           end
172   
173           /*
174           **  Unbind it.
175           */
176   
177           /* 
178           ** This transaction also writes a log record for replicating the
179           ** invocation of this procedure. If logexec() fails, the transaction
180           ** is aborted.
181           **
182           ** IMPORTANT: The name rs_logexec is significant and is used by
183           ** Replication Server.
184           */
185           begin transaction rs_logexec
186   
187           update syscolumns
188           set cdefault = 0
189           from syscolumns, sysobjects
190           where syscolumns.id = object_id(@tabname)
191               and syscolumns.name = @colname
192               and sysobjects.id = object_id(@tabname)
193               and uid = user_id()
194               and sysobjects.sysstat & 7 = 3
195   
196           /*
197           **  Since binding a default is a schema change,
198           **  update schema count
199           **  for the object in the sysobjects table.
200           */
201   
202           if (schema_inc(object_id(@tabname), 0) != 1)
203           begin
204               /*
205               ** 17763, "The execution of the stored procedure '%1!'
206               **         in database '%2!' was aborted because there
207               **         was an error in updating the column
208               **         schemacnt in sysobjects."
209               */
210               select @dbname = db_name()
211               raiserror 17763, "sp_unbindefault", @dbname
212               rollback transaction rs_logexec
213               return (1)
214           end
215   
216           /*
217           ** Write the log record to replicate this invocation 
218           ** of the stored procedure.
219           */
220           if (logexec() != 1)
221           begin
222               /*
223               ** 17756, "The execution of the stored procedure '%1!'
224               ** 	   in database '%2!' was aborted because there
225               ** 	   was an error in writing the replication log
226               **	   record."
227               */
228               select @dbname = db_name()
229               raiserror 17756, "sp_unbindefault", @dbname
230   
231               rollback transaction rs_logexec
232               return (1)
233           end
234   
235           commit transaction
236   
237           /*
238           ** 17841, "Default unbound from table column."
239           */
240           exec sp_getmessage 17841, @msg output
241           print @msg
242           return (0)
243       end
244       else
245       begin
246           /*
247           **  We're unbinding to a user type.  In this case, the @objname
248           **  is really the name of the user datatype.
249           **  When we unbind to a user type, any existing columns get changed
250           **  to the new binding unless their current binding is not equal
251           **  to the current binding for the usertype or if they set the
252           **  @futureonly parameter to @futurevalue.
253           */
254           declare @olddefault int /* current default for type */
255   
256           /*
257           **  Get the current default for the datatype.
258           */
259           select @olddefault = tdefault
260           from systypes
261           where name = @objname
262               and uid = user_id()
263               and usertype > 100
264   
265           if @olddefault is null
266           begin
267               /*
268               ** 17844, "You do not own a user datatype of that name."
269               */
270   
271               /* Audit the failure to unbind a default */
272               select @returncode =
273                   ad_hoc_audit(@eventnum, @mod_fail, NULL, db_name(),
274                       @objname, user_name(), 0, object_id(@objname)
275   
276                   )
277               raiserror 17844
278               return (1)
279           end
280   
281   
282           /* Audit the succssful permission to unbind a default */
283           select @returncode =
284               ad_hoc_audit(@eventnum, @mod_ok, NULL, db_name(),
285                   @objname, user_name(), 0, object_id(@objname)
286   
287               )
288           if (@returncode != 0)
289           begin
290               /* 
291               ** 18293, "Auditing for '%1!' event has failed due to 
292               ** internal error. Contact a user with System Security 
293               ** Officer (SSO) role."
294               */
295               raiserror 18293, @eventnum
296               return (1)
297           end
298           if @olddefault = 0
299           begin
300               /*
301               ** 17845, "The specified user datatype has no default."
302               */
303               raiserror 17845
304               return (1)
305           end
306   
307           /* 
308           ** This transaction also writes a log record for
309           ** replicating the invocation of this procedure.
310           ** If logexec() fails, the transaction is aborted.
311           **
312           ** IMPORTANT: The name rs_logexec is significant and is
313           ** used by Replication Server.
314           */
315           begin transaction rs_logexec
316   
317           update systypes
318           set tdefault = 0
319           from systypes
320           where name = @objname
321               and uid = user_id()
322               and usertype > 100
323   
324           /*
325           ** 17846, "Default unbound from datatype."
326           */
327           exec sp_getmessage 17846, @msg output
328           print @msg
329   
330           /*
331           **  Now see if there are any columns with the usertype that
332           **  need the new binding.
333           */
334   
335           select @rows_selected = 0
336           if isnull(@futureonly, "") != @futurevalue
337           begin
338               select @rows_selected = count(distinct syscolumns.id)
339               from syscolumns, systypes
340               where syscolumns.usertype = systypes.usertype
341                   and systypes.name = @objname
342                   and systypes.usertype > 100
343                   and systypes.uid = user_id()
344                   and syscolumns.cdefault = @olddefault
345   
346               if (@rows_selected > 0)
347               begin
348                   /*
349                   **  Update the table schema to indicate that something
350                   **  has changed in the table's schema.
351                   */
352                   select @tmp = sum(schema_inc(s.id, 0))
353                   from sysobjects s
354                   where exists
355                           (select 1
356                           from syscolumns, systypes
357                           where s.id = syscolumns.id
358                               and syscolumns.usertype = systypes.usertype
359                               and systypes.name = @objname
360                               and systypes.usertype > 100
361                               and systypes.uid = user_id()
362                               and syscolumns.cdefault = @olddefault)
363   
364                   if (@rows_selected != @tmp)
365                   begin
366                       /*
367                       ** 17763, "The execution of the stored procedure '%1!'
368                       **         in database '%2!' was aborted because there
369                       **         was an error in updating the column
370                       **         schemacnt in sysobjects."
371                       */
372                       select @dbname = db_name()
373                       raiserror 17763, "sp_unbindefault", @dbname
374                       rollback transaction rs_logexec
375                       return (1)
376                   end
377   
378                   /*
379                   **  Update syscolumns with new binding.
380                   */
381                   update syscolumns
382                   set cdefault = systypes.tdefault
383                   from syscolumns, systypes
384                   where syscolumns.usertype = systypes.usertype
385                       and systypes.name = @objname
386                       and systypes.usertype > 100
387                       and systypes.uid = user_id()
388                       and syscolumns.cdefault = @olddefault
389               end
390           end
391   
392           /*
393           ** Write the log record to replicate this invocation 
394           ** of the stored procedure.
395           */
396           if (logexec() != 1)
397           begin
398               /*
399               ** 17756, "The execution of the stored procedure
400               **         '%1!' in database '%2!' was aborted
401               **	    because there was an error in writing
402               **	    the replication log record."
403               */
404               select @dbname = db_name()
405               raiserror 17756, "sp_unbindefault", @dbname
406   
407               rollback transaction rs_logexec
408               return (1)
409           end
410   
411           commit transaction
412   
413           /*
414           ** 17847, "Columns of the user datatype specified had
415           ** their defaults unbound."
416           */
417           exec sp_getmessage 17847, @msg output
418           print @msg
419   
420       end
421   
422       return (0)
423   


exec sp_procxmode 'sp_unbindefault', 'AnyMode'
go

Grant Execute on sp_unbindefault to public
go
DEFECTS
 MCTR 4 Conditional Begin Tran or Commit Tran 185
 MCTR 4 Conditional Begin Tran or Commit Tran 235
 MCTR 4 Conditional Begin Tran or Commit Tran 315
 MCTR 4 Conditional Begin Tran or Commit Tran 411
 MEST 4 Empty String will be replaced by Single Space 336
 QCAS 4 Cartesian product with single row between tables sybsystemprocs..syscolumns and [sybsystemprocs..sysobjects], 3 tables with rc=1 100
 QCAS 4 Cartesian product with single row between tables sybsystemprocs..syscolumns and [sybsystemprocs..sysobjects], 3 tables with rc=1 187
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 263
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 322
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 342
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 360
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 386
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 102
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 104
 MAW1 3 Warning message on %name% sybsystemprocs..sysprocedures.id: Warning message on sysprocedures 158
 MAW1 3 Warning message on %name% sybsystemprocs..syscomments.id: Warning message on syscomments 162
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 190
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 192
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 338
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 352
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 357
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 357
 MGTP 3 Grant to public sybsystemprocs..sp_unbindefault  
 MGTP 3 Grant to public sybsystemprocs..syscolumns  
 MGTP 3 Grant to public sybsystemprocs..syscomments  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MGTP 3 Grant to public sybsystemprocs..sysprocedures  
 MGTP 3 Grant to public sybsystemprocs..systypes  
 MNER 3 No Error Check should check @@error after update 187
 MNER 3 No Error Check should check return value of exec 240
 MNER 3 No Error Check should check @@error after update 317
 MNER 3 No Error Check should check return value of exec 327
 MNER 3 No Error Check should check @@error after update 381
 MNER 3 No Error Check should check return value of exec 417
 MUCO 3 Useless Code Useless Brackets 77
 MUCO 3 Useless Code Useless Brackets 121
 MUCO 3 Useless Code Useless Brackets 132
 MUCO 3 Useless Code Useless Brackets 140
 MUCO 3 Useless Code Useless Brackets 151
 MUCO 3 Useless Code Useless Brackets 170
 MUCO 3 Useless Code Useless Brackets 202
 MUCO 3 Useless Code Useless Brackets 213
 MUCO 3 Useless Code Useless Brackets 220
 MUCO 3 Useless Code Useless Brackets 232
 MUCO 3 Useless Code Useless Brackets 242
 MUCO 3 Useless Code Useless Brackets 278
 MUCO 3 Useless Code Useless Brackets 288
 MUCO 3 Useless Code Useless Brackets 296
 MUCO 3 Useless Code Useless Brackets 304
 MUCO 3 Useless Code Useless Brackets 346
 MUCO 3 Useless Code Useless Brackets 364
 MUCO 3 Useless Code Useless Brackets 375
 MUCO 3 Useless Code Useless Brackets 396
 MUCO 3 Useless Code Useless Brackets 408
 MUCO 3 Useless Code Useless Brackets 422
 MUUF 3 Update or Delete with Useless From Clause 317
 QISO 3 Set isolation level 53
 QISO 3 Set isolation level 55
 QNAJ 3 Not using ANSI Inner Join 101
 QNAJ 3 Not using ANSI Inner Join 189
 QNAJ 3 Not using ANSI Inner Join 339
 QNAJ 3 Not using ANSI Inner Join 356
 QNAJ 3 Not using ANSI Inner Join 383
 QNUA 3 Should use Alias: Table sybsystemprocs..syscolumns 101
 QNUA 3 Should use Alias: Table sybsystemprocs..sysobjects 101
 QNUA 3 Should use Alias: Column uid should use alias sysobjects 105
 QNUA 3 Should use Alias: Table sybsystemprocs..sysobjects 189
 QNUA 3 Should use Alias: Column uid should use alias sysobjects 193
 QNUA 3 Should use Alias: Table sybsystemprocs..syscolumns 339
 QNUA 3 Should use Alias: Table sybsystemprocs..systypes 339
 QNUA 3 Should use Alias: Table sybsystemprocs..syscolumns 356
 QNUA 3 Should use Alias: Table sybsystemprocs..systypes 356
 QNUA 3 Should use Alias: Table sybsystemprocs..systypes 383
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
102
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysprocedures.csysprocedures unique clustered
(id, number, type, sequence)
Intersection: {id, sequence}
158
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscomments.csyscomments unique clustered
(id, number, colid2, colid, texttype)
Intersection: {id}
162
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
190
 MSUB 2 Subquery Marker 157
 MSUB 2 Subquery Marker 161
 MSUC 2 Correlated Subquery Marker 355
 MTR1 2 Metrics: Comments Ratio Comments: 52% 24
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 24 = 36dec - 14exi + 2 24
 MTR3 2 Metrics: Query Complexity Complexity: 168 24
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, t=sybsystemprocs..systypes} 0 338
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, o=sybsystemprocs..sysobjects, t=sybsystemprocs..systypes} 0 355
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, t=sybsystemprocs..systypes} 0 381

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..sysobjects  
reads table sybsystemprocs..syscomments  
calls proc sybsystemprocs..sp_getmessage  
   reads table master..syslanguages (1)  
   reads table sybsystemprocs..sysusermessages  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..sysmessages (1)  
reads table sybsystemprocs..sysprocedures  
read_writes table sybsystemprocs..systypes  
read_writes table sybsystemprocs..syscolumns