DatabaseProcApplicationCreatedLinks
sybsystemprocssp_bindefault  31 Aug 14Defects Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
3     /*	4.8	1.1	06/14/90	sproc/src/bindefault */
4     
5     /*
6     ** Messages for "sp_bindefault"         17340
7     **
8     ** 17340, "Default and table or usertype must be in 'current' database."
9     ** 17341, "Usage: bindefault defaultname, objectname [, 'futureonly']"
10    ** 17342, "No such default exists.  You must create the default first."
11    ** 17343, "You can't bind a default to a timestamp datatype column."
12    ** 17344, "You do not own a column of that name."
13    ** 17345, "Default bound to column."
14    ** 17346, "You do not own a datatype of that name."
15    ** 17347, "Default bound to datatype."
16    ** 17348, "The new default has been bound to columns(s) of the specified user datatype."
17    ** 17349, "You cannot bind a declared default. The default must be created using create default."
18    ** 17753, "The column already has a default. Bind disallowed."
19    ** 17756, "The execution of the stored procedure '%1!' in database
20    **         '%2!' was aborted because there was an error in writing the
21    **         replication log record."
22    ** 17763, "The execution of the stored procedure '%1!' in database '%2!' was aborted because there
23    **         was an error in updating the schemacnt column in sysobjects."
24    ** 18293, "Auditing for '%1!' event has failed due to internal error. Contact a user with System Security Officer (SSO) role."
25    ** 19358, "You cannot bind a default to a computed column."
26    */
27    
28    /*
29    ** IMPORTANT NOTE:
30    ** This stored procedure uses the built-in function object_id() in the
31    ** where clause of a select query. If you intend to change this query
32    ** or use the object_id() or db_id() builtin in this procedure, please read the
33    ** READ.ME file in the $DBMS/generic/sproc directory to ensure that the rules
34    ** pertaining to object-id's and db-id's outlined there, are followed.
35    */
36    
37    create procedure sp_bindefault
38        @defname varchar(767), /* name of the default */
39        @objname varchar(511), /* table or usertype name */
40        @futureonly varchar(15) = NULL /* flag to indicate extent of binding */
41    as
42    
43        declare @futurevalue varchar(15) /* the value of @futureonly that causes
44        ** the binding to be limited */
45        declare @msg varchar(1024)
46        declare @returncode int /* return from ad_hoc_audit builtin */
47        declare @eventnum int /* event number for bind default auditing */
48        declare @mod_ok int /* successful bind default auditing  */
49        declare @mod_fail int /* failure bind default auditing  */
50        declare @maxobjlen int /* get the length of sysobject.name from syscolumns */
51        declare @maxuserlen int /* get the length of sysusers.name from syscolumns */
52        declare @maxtotlen int /* get the Total length of sysobjects.name + sysusers.name*/
53    
54    
55        declare @rows_selected int
56        declare @tmp int
57    
58        select @eventnum = 6 /* event number for unbind default */
59        select @mod_ok = 1
60        select @mod_fail = 2
61        declare @dbname varchar(255)
62    
63    
64    
65        if @@trancount = 0
66        begin
67            set chained off
68        end
69    
70        set transaction isolation level 1
71    
72        set nocount on
73    
74        select @futurevalue = "futureonly" /* initialize @futurevalue */
75    
76        /*
77        **  When a default or rule is bound to a user-defined datatype, it is also
78        **  bound, by default, to any columns of the user datatype that are currently
79        **  using the existing default or rule as their default or rule.  This default
80        **  action may be overridden by setting @futureonly = @futurevalue when the
81        **  procedure is invoked.  In this case existing columns with the user
82        **  datatype won't have their existing default or rule changed.
83        */
84    
85        /*
86        **  Check to see that the object names are local to the current database.
87        */
88        if (@objname like "%.%.%")
89            or
90            (@defname like "%.%.%" and
91                substring(@defname, 1, charindex(".", @defname) - 1) != db_name())
92        begin
93            /*
94            ** 17340, "Default and table or usertype must be in 'current' database."
95            */
96            raiserror 17340
97            return (1)
98        end
99    
100       /*
101       **  Check that the @futureonly argument, if supplied, is correct.
102       */
103       if (@futureonly is not null)
104       begin
105           if (@futureonly != @futurevalue)
106           begin
107               /*
108               ** 17341, "Usage: bindefault defaultname, objectname [, 'futureonly']"
109               */
110               raiserror 17341
111               return (1)
112           end
113       end
114   
115       /*
116       **  Check to see that the default exists and get it's id.
117       */
118       if not exists (select id
119               from sysobjects
120               where id = object_id(@defname)
121                   and sysstat & 7 = 6) /* default object */
122       begin
123           /*
124           ** 17342, "No such default exists.  You must create the default first."
125           */
126           raiserror 17342
127           return (1)
128       end
129   
130       /*
131       ** Check to see that the default is not of declared type
132       */
133   
134       if (exists (select * from sysprocedures
135                   where id = object_id(@defname)
136                       and sequence = 0
137                       and status & 4096 = 4096)
138           ) or (exists (select * from syscomments
139                   where id = object_id(@defname)
140                       and status & 8 = 8) -- sharable inline default
141           )
142       begin
143           /*
144           ** 17349, "You cannot bind a declared default. The default must be created \
145           using create default."
146           */
147           raiserror 17349
148           return (1)
149       end
150   
151       /*
152       **  If @objname is of the form tab.col then we are binding to a column.
153       **  Otherwise its a datatype.  In the column case, we need to extract
154       **  and verify the table and column names and make sure the user owns
155       **  the table that is getting the default bound.
156       */
157       if @objname like "%.%"
158       begin
159           declare @tabname varchar(255) /* name of table */
160           declare @colname varchar(255) /* name of column */
161           select @maxobjlen = length from syscolumns
162           where id = object_id("sysobjects") and name = "name"
163           select @maxuserlen = length from syscolumns
164           where id = object_id("sysusers") and name = "name"
165           select @maxtotlen = (@maxobjlen + @maxuserlen) + 1
166   
167   
168           /*
169           **  Get the table name out.
170           */
171           select @tabname = substring(@objname, 1, charindex(".", @objname) - 1)
172           select @colname = substring(@objname, charindex(".", @objname) + 1, @maxtotlen)
173   
174           /*
175           **  If the column type is timestamp, disallow the bind.
176           **  Defaults can't be bound to timestamp columns.
177           */
178           if exists (select *
179                   from sysobjects o, syscolumns c
180                   where c.id = object_id(@tabname)
181                       and c.name = @colname
182                       and c.name = @colname
183                       and o.id = object_id(@tabname)
184                       and o.sysstat & 7 = 3
185                       and c.usertype = 80)
186           begin
187               /*
188               ** 17343, "You can't bind a default to a timestamp datatype column."
189               */
190               raiserror 17343
191               return (1)
192           end
193   
194           /* 
195           ** Disallow binding defaults on computed columns
196           */
197           if exists (select 1 from syscolumns c
198                   where c.id = object_id(@tabname)
199                       and c.name = @colname
200                       and c.status2 & 16 = 16)
201           begin
202               /*
203               ** 19358, "You cannot bind a default to a computed column."
204               */
205               raiserror 19358
206               return (1)
207           end
208   
209           /*
210           ** If the column already has a default, then disallow the bind
211           */
212           if exists (select * from syscolumns c
213                   where c.id = object_id(@tabname)
214                       and c.name = @colname
215                       and c.cdefault != 0)
216           begin
217               /*
218               ** 17753, "The column already has a default. Bind disallowed."
219               */
220               raiserror 17753
221               return (1)
222           end
223   
224           begin transaction
225           update syscolumns
226           set cdefault = object_id(@defname)
227           from syscolumns, sysobjects
228           where syscolumns.id = object_id(@tabname)
229               and syscolumns.name = @colname
230               and sysobjects.id = object_id(@tabname)
231               and uid = user_id()
232               and sysobjects.sysstat & 7 = 3 /* user table */
233   
234           /*
235           **  Did the bind happen?
236           */
237           if @@rowcount != 1
238           begin
239               /*
240               ** 17344, "You do not own a column of that name."
241               */
242               /* Audit the failure to bind a default */
243   
244               rollback transaction
245   
246               select @returncode =
247                   ad_hoc_audit(@eventnum, @mod_fail, @defname, db_name(),
248                       @tabname, user_name(), 0, object_id(@tabname)
249   
250                   )
251               raiserror 17344
252               return (1)
253           end
254   
255           /* Audit the successful permission to bind a default */
256           select @returncode =
257               ad_hoc_audit(@eventnum, @mod_ok, @defname, db_name(),
258                   @tabname, user_name(), 0, object_id(@tabname)
259   
260               )
261           if (@returncode != 0)
262           begin
263               rollback transaction
264               /* 
265               ** 18293, "Auditing for '%1!' event has failed due to 
266               ** internal error. Contact a user with System Security 
267               ** Officer (SSO) role."
268               */
269               raiserror 18293, @eventnum
270               return (1)
271           end
272   
273           commit transaction
274           /*
275           **  Since binding a default is a schema change, update schema count
276           **  for the object in the sysobjects table.
277           */
278   
279           /* 
280           ** This transaction also writes a log record for replicating the
281           ** invocation of this procedure. If logexec() fails, the transaction
282           ** is aborted.
283           **
284           ** IMPORTANT: The name rs_logexec is significant and is used by
285           ** Replication Server.
286           */
287           begin transaction rs_logexec
288   
289           if (schema_inc(object_id(@tabname), 0) != 1)
290           begin
291               /*
292               ** 17763, "The execution of the stored procedure '%1!'
293               **         in database '%2!' was aborted because there
294               **         was an error in updating the column
295               **         schemacnt in sysobjects."
296               */
297               select @dbname = db_name()
298               raiserror 17763, "sp_bindefault", @dbname
299               rollback transaction rs_logexec
300               return (1)
301           end
302   
303           if (logexec() != 1)
304           begin
305               /*
306               ** 17756, "The execution of the stored procedure '%1!'
307               ** 	   in database '%2!' was aborted because there
308               ** 	   was an error in writing the replication log
309               **	   record."
310               */
311               select @dbname = db_name()
312               raiserror 17756, "sp_bindefault", @dbname
313   
314               rollback transaction rs_logexec
315               return (1)
316           end
317   
318           commit transaction
319   
320           /*
321           ** 17345, "Default bound to column."
322           */
323           exec sp_getmessage 17345, @msg output
324           print @msg
325       end
326       else
327       begin
328           /*
329           **  We're binding to a user type.  In this case, the @objname
330           **  is really the name of the user datatype.
331           **  When we bind to a user type, any existing columns get changed
332           **  to the new binding unless they set the
333           **  @futureonly parameter to @futurevalue.
334           */
335           /*
336           **  Get the current default for the datatype.
337           */
338           if not exists (select tdefault
339                   from systypes
340                   where name = @objname
341                       and uid = user_id() and usertype > 100)
342           begin
343               /*
344               ** 17346, "You do not own a datatype of that name."
345               */
346   
347               /* Audit the failure to bind a default */
348               select @returncode =
349                   ad_hoc_audit(@eventnum, @mod_fail, @defname, db_name(),
350                       @objname, user_name(), 0, object_id(@objname)
351   
352                   )
353               raiserror 17346
354               return (1)
355           end
356   
357           /* Audit the successful permission to bind a default */
358           select @returncode =
359               ad_hoc_audit(@eventnum, @mod_ok, @defname, db_name(),
360                   @objname, user_name(), 0, object_id(@objname)
361   
362               )
363           if (@returncode != 0)
364           begin
365               /* 	AUDIT_CHANGE	
366               **	Bind auditing has failed due to internal error contact 
367               **	your SSO 
368               */
369               print "Bind auditing has failed due to internal error contact SSO user"
370               return (1)
371           end
372   
373           /* 
374           ** This transaction also writes a log record for
375           ** replicating the invocation of this procedure. If
376           ** logexec() fails, the transaction is aborted.
377           **
378           ** IMPORTANT: The name rs_logexec is significant and
379           **  is used by Replication Server.
380           */
381           begin transaction rs_logexec
382   
383           update systypes
384           set tdefault = object_id(@defname)
385           from systypes
386           where name = @objname
387               and uid = user_id()
388               and usertype > 100
389   
390   
391           /*
392           ** 17347, "Default bound to datatype."
393           */
394           exec sp_getmessage 17347, @msg output
395           print @msg
396   
397           /*
398           **  Now see if there are any columns with the usertype that
399           **  need the new binding.
400           */
401   
402           select @rows_selected = 0
403           if isnull(@futureonly, "") != @futurevalue
404           begin
405               select @rows_selected = count(distinct syscolumns.id)
406               from syscolumns, systypes
407               where syscolumns.usertype = systypes.usertype
408                   and systypes.name = @objname
409                   and systypes.usertype > 100
410                   and systypes.uid = user_id()
411               if (@rows_selected > 0)
412               begin
413                   /*
414                   **  Update the table schema to indicate that something
415                   **  has changed in the table's schema.
416                   */
417                   select @tmp = sum(schema_inc(s.id, 0))
418                   from sysobjects s
419                   where exists
420                           (select 1
421                           from syscolumns, systypes
422                           where syscolumns.usertype = systypes.usertype
423                               and syscolumns.id = s.id
424                               and systypes.name = @objname
425                               and systypes.usertype > 100
426                               and systypes.uid = user_id())
427   
428                   if (@rows_selected != @tmp)
429                   begin
430                       /*
431                       ** 17763, "The execution of the stored procedure '%1!'
432                       **         in database '%2!' was aborted because there
433                       **         was an error in updating the column
434                       **         schemacnt in sysobjects."
435                       */
436                       select @dbname = db_name()
437                       raiserror 17763, "sp_bindefault", @dbname
438                       rollback transaction rs_logexec
439                       return (1)
440                   end
441   
442                   /*
443                   **  Update syscolumns with new binding.
444                   */
445                   update syscolumns
446                   set cdefault = systypes.tdefault
447                   from syscolumns, systypes
448                   where syscolumns.usertype = systypes.usertype
449                       and systypes.name = @objname
450                       and systypes.usertype > 100
451                       and systypes.uid = user_id()
452               end
453           end
454   
455           /*
456           ** Write the log record to replicate this invocation 
457           ** of the stored procedure.
458           */
459           if (logexec() != 1)
460           begin
461               /*
462               ** 17756, "The execution of the stored procedure
463               **         '%1!' in database '%2!' was aborted
464               **	    because there was an error in writing
465               **	    the replication log record."
466               */
467               select @dbname = db_name()
468               raiserror 17756, "sp_bindefault", @dbname
469   
470               rollback transaction rs_logexec
471               return (1)
472           end
473   
474           commit transaction
475   
476           /*
477           ** 17348, "The new default has been bound to columns(s) of the specified user datatype."
478           */
479           exec sp_getmessage 17348, @msg output
480           print @msg
481   
482       end
483   
484       return (0)
485   


exec sp_procxmode 'sp_bindefault', 'AnyMode'
go

Grant Execute on sp_bindefault to public
go
DEFECTS
 MCTR 4 Conditional Begin Tran or Commit Tran 224
 MCTR 4 Conditional Begin Tran or Commit Tran 273
 MCTR 4 Conditional Begin Tran or Commit Tran 287
 MCTR 4 Conditional Begin Tran or Commit Tran 318
 MCTR 4 Conditional Begin Tran or Commit Tran 381
 MCTR 4 Conditional Begin Tran or Commit Tran 474
 MEST 4 Empty String will be replaced by Single Space 403
 QCAS 4 Cartesian product with single row between tables sybsystemprocs..sysobjects o and [sybsystemprocs..syscolumns c], 2 tables with rc=1 178
 QCAS 4 Cartesian product with single row between tables sybsystemprocs..syscolumns and [sybsystemprocs..sysobjects], 3 tables with rc=1 225
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 185
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 341
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 388
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 409
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 425
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 450
 MGTP 3 Grant to public sybsystemprocs..sp_bindefault  
 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 225
 MNER 3 No Error Check should check return value of exec 323
 MNER 3 No Error Check should check @@error after update 383
 MNER 3 No Error Check should check return value of exec 394
 MNER 3 No Error Check should check @@error after update 445
 MNER 3 No Error Check should check return value of exec 479
 MUCO 3 Useless Code Useless Brackets 97
 MUCO 3 Useless Code Useless Brackets 103
 MUCO 3 Useless Code Useless Brackets 105
 MUCO 3 Useless Code Useless Brackets 111
 MUCO 3 Useless Code Useless Brackets 127
 MUCO 3 Useless Code Useless Brackets 148
 MUCO 3 Useless Code Useless Brackets 191
 MUCO 3 Useless Code Useless Brackets 206
 MUCO 3 Useless Code Useless Brackets 221
 MUCO 3 Useless Code Useless Brackets 252
 MUCO 3 Useless Code Useless Brackets 261
 MUCO 3 Useless Code Useless Brackets 270
 MUCO 3 Useless Code Useless Brackets 289
 MUCO 3 Useless Code Useless Brackets 300
 MUCO 3 Useless Code Useless Brackets 303
 MUCO 3 Useless Code Useless Brackets 315
 MUCO 3 Useless Code Useless Brackets 354
 MUCO 3 Useless Code Useless Brackets 363
 MUCO 3 Useless Code Useless Brackets 370
 MUCO 3 Useless Code Useless Brackets 411
 MUCO 3 Useless Code Useless Brackets 428
 MUCO 3 Useless Code Useless Brackets 439
 MUCO 3 Useless Code Useless Brackets 459
 MUCO 3 Useless Code Useless Brackets 471
 MUCO 3 Useless Code Useless Brackets 484
 MUUF 3 Update or Delete with Useless From Clause 383
 QAFM 3 Var Assignment from potentially many rows 161
 QAFM 3 Var Assignment from potentially many rows 163
 QISO 3 Set isolation level 70
 QNAJ 3 Not using ANSI Inner Join 179
 QNAJ 3 Not using ANSI Inner Join 227
 QNAJ 3 Not using ANSI Inner Join 406
 QNAJ 3 Not using ANSI Inner Join 421
 QNAJ 3 Not using ANSI Inner Join 447
 QNUA 3 Should use Alias: Table sybsystemprocs..sysobjects 227
 QNUA 3 Should use Alias: Column uid should use alias sysobjects 231
 QNUA 3 Should use Alias: Table sybsystemprocs..syscolumns 406
 QNUA 3 Should use Alias: Table sybsystemprocs..systypes 406
 QNUA 3 Should use Alias: Table sybsystemprocs..syscolumns 421
 QNUA 3 Should use Alias: Table sybsystemprocs..systypes 421
 QNUA 3 Should use Alias: Table sybsystemprocs..systypes 447
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysprocedures.csysprocedures unique clustered
(id, number, type, sequence)
Intersection: {id, sequence}
135
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscomments.csyscomments unique clustered
(id, number, colid2, colid, texttype)
Intersection: {id}
139
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
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}
164
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
180
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
198
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
213
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
228
 QRPR 3 Repeated predicate c.name = @colname 182
 MSUB 2 Subquery Marker 118
 MSUB 2 Subquery Marker 134
 MSUB 2 Subquery Marker 138
 MSUB 2 Subquery Marker 178
 MSUB 2 Subquery Marker 197
 MSUB 2 Subquery Marker 212
 MSUB 2 Subquery Marker 338
 MSUC 2 Correlated Subquery Marker 420
 MTR1 2 Metrics: Comments Ratio Comments: 52% 37
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 33 = 47dec - 16exi + 2 37
 MTR3 2 Metrics: Query Complexity Complexity: 204 37
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, t=sybsystemprocs..systypes} 0 405
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, o=sybsystemprocs..sysobjects, t=sybsystemprocs..systypes} 0 420
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, t=sybsystemprocs..systypes} 0 445

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