Database | Proc | Application | Created | Links |
sybsystemprocs | sp_bindefault ![]() | ![]() | 31 Aug 14 | Defects 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
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 ![]() |