| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_unbindefault | | 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/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 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 |
 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: 23 = 35dec - 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..syscomments 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 |