| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_dropkey | | 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/defaultlanguage */ 4 5 /* 6 ** Messages for "sp_dropkey" 17490 7 ** 8 ** 17390, "Table or view name must be in 'current' database." 9 ** 17490, "Usage: sp_dropkey {primary | foreign | common}, tabname [,deptabname]." 10 ** 17491, "Type must be 'primary', 'foreign', or 'common'." 11 ** 17492, "The table or view named doesn't exist in the current database." 12 ** 17493, "You must be the owner of the table or view to drop its key." 13 ** 17494, "No primary key for the table or view exists." 14 ** 17495, "Primary key for the table or view dropped." 15 ** 17496, "Dependent foreign keys were also dropped." 16 ** 17497, "You need to supply the dependent table or view as the third parameter." 17 ** 17498, "The dependent table or view doesn't exist in the current database." 18 ** 17499, "No foreign key for the table or view exists." 19 ** 17500, "Foreign key dropped." 20 ** 17501, "No common keys exist between the two tables or views supplied." 21 ** 17502, "Common keys dropped." 22 ** 17756, "The execution of the stored procedure '%1!' in database 23 ** '%2!' was aborted because there was an error in writing the 24 ** replication log record." 25 */ 26 27 /* 28 ** IMPORTANT NOTE: 29 ** This stored procedure uses the built-in function object_id() in the 30 ** where clause of a select query. If you intend to change this query 31 ** or use the object_id() or db_id() builtin in this procedure, please read the 32 ** READ.ME file in the $DBMS/generic/sproc directory to ensure that the rules 33 ** pertaining to object-id's and db-id's outlined there, are followed. 34 */ 35 36 create procedure sp_dropkey 37 @keytype varchar(10), /* type of key to drop */ 38 @tabname varchar(767), /* table with the key */ 39 @deptabname varchar(767) = null /* dependent table */ 40 as 41 42 declare @uid int /* owner of @tabname */ 43 declare @msg varchar(1024) 44 declare @dbname varchar(255) 45 46 47 if @@trancount = 0 48 begin 49 set chained off 50 end 51 52 set transaction isolation level 1 53 54 /* 55 ** First make sure that the key type is ok. 56 */ 57 if @keytype not in ("primary", "foreign", "common") 58 begin 59 /* 60 ** 17490, "Usage: sp_dropkey {primary | foreign | common}, tabname [,deptabname]." 61 */ 62 raiserror 17490 63 /* 64 ** 17491, "Type must be 'primary', 'foreign', or 'common'." 65 */ 66 raiserror 17491 67 return (1) 68 end 69 70 /* 71 ** Check to see that the tabname is local. 72 */ 73 if @tabname like "%.%.%" 74 begin 75 if substring(@tabname, 1, charindex(".", @tabname) - 1) != db_name() 76 begin 77 /* 78 ** 17390, "Table or view name must be in 'current' database." 79 */ 80 raiserror 17390 81 return (1) 82 end 83 end 84 if @deptabname like "%.%.%" 85 begin 86 if substring(@deptabname, 1, charindex(".", @deptabname) - 1) != db_name() 87 begin 88 /* 89 ** 17390, "Table or view name must be in 'current' database." 90 */ 91 raiserror 17390 92 return (1) 93 end 94 end 95 96 /* 97 ** Get the ids of the @tabname and @deptabname. 98 */ 99 if not exists (select * 100 from sysobjects 101 where id = object_id(@tabname)) 102 begin 103 /* 104 ** 17492, "The table or view named doesn't exist in the current database." 105 */ 106 raiserror 17492 107 return (1) 108 end 109 110 /* 111 ** Get the user-id of @tabname 112 */ 113 select @uid = uid 114 from sysobjects 115 where id = object_id(@tabname) 116 117 if @uid != user_id() 118 begin 119 /* 120 ** 17493, "You must be the owner of the table or view to drop its key." 121 */ 122 raiserror 17493 123 return (1) 124 end 125 126 /* 127 ** If primary key, just drop it. 128 */ 129 if @keytype = "primary" 130 begin 131 delete from syskeys 132 where id = object_id(@tabname) 133 and type = 1 134 135 if @@rowcount = 0 136 begin 137 /* 138 ** 17494, "No primary key for the table or view exists." 139 */ 140 raiserror 17494 141 return (1) 142 end 143 144 else 145 begin 146 /* 147 ** 17495, "Primary key for the table or view dropped." 148 */ 149 exec sp_getmessage 17495, @msg output 150 print @msg 151 end 152 153 /* 154 ** Check to see if there are any foreign keys dependent on the 155 ** primary key. If so -- drop them. 156 */ 157 /* 158 ** This transaction also writes a log record for replicating the 159 ** invocation of this procedure. If logexec() fails, the transaction 160 ** is aborted. 161 ** 162 ** IMPORTANT: The name rs_logexec is significant and is used by 163 ** Replication Server. 164 */ 165 begin transaction rs_logexec 166 167 delete from syskeys 168 where depid = object_id(@tabname) 169 and type = 2 170 171 if @@rowcount != 0 172 begin 173 /* 174 ** 17496, "Dependent foreign keys were also dropped." 175 */ 176 exec sp_getmessage 17496, @msg output 177 print @msg 178 end 179 180 /* 181 ** Write the log record to replicate this invocation 182 ** of the stored procedure. 183 */ 184 if (logexec() != 1) 185 begin 186 /* 187 ** 17756, "The execution of the stored procedure '%1!' 188 ** in database '%2!' was aborted because there 189 ** was an error in writing the replication log 190 ** record." 191 */ 192 select @dbname = db_name() 193 raiserror 17756, "sp_dropkey", @dbname 194 195 rollback transaction rs_logexec 196 return (1) 197 end 198 199 commit transaction 200 201 return (0) 202 end 203 204 /* 205 ** It's either a foreign or common key so we need to verify the 206 ** existence of the @deptabname. 207 */ 208 if not exists (select id 209 from sysobjects 210 where id = object_id(@deptabname)) 211 begin 212 /* 213 ** Was the @deptabname supplied? 214 */ 215 if @deptabname is null 216 begin 217 /* 218 ** 17497, "You need to supply the dependent table or view as the third parameter." 219 */ 220 raiserror 17497 221 return (1) 222 end 223 224 /* 225 ** It was supplied but it doesn't exist. 226 */ 227 /* 228 ** 17498, "The dependent table or view doesn't exist in the current database." 229 */ 230 raiserror 17498 231 return (1) 232 end 233 234 235 /* 236 ** If foreign key, get rid of it. 237 */ 238 if @keytype = "foreign" 239 begin 240 /* 241 ** Get rid of the foreign key entry. 242 */ 243 244 /* 245 ** This transaction also writes a log record for replicating the 246 ** invocation of this procedure. If logexec() fails, the transaction 247 ** is aborted. 248 ** 249 ** IMPORTANT: The name rs_logexec is significant and is used by 250 ** Replication Server. 251 */ 252 begin transaction rs_logexec 253 254 delete from syskeys 255 where type = 2 256 and id = object_id(@tabname) 257 and depid = object_id(@deptabname) 258 if @@rowcount = 0 259 begin 260 /* 261 ** 17499, "No foreign key for the table or view exists." 262 */ 263 raiserror 17499 264 265 rollback transaction rs_logexec 266 return (1) 267 end 268 269 /* 270 ** Write the log record to replicate this invocation 271 ** of the stored procedure. 272 */ 273 if (logexec() != 1) 274 begin 275 /* 276 ** 17756, "The execution of the stored procedure '%1!' 277 ** in database '%2!' was aborted because there 278 ** was an error in writing the replication log 279 ** record." 280 */ 281 select @dbname = db_name() 282 raiserror 17756, "sp_dropkey", @dbname 283 284 rollback transaction rs_logexec 285 return (1) 286 end 287 288 commit transaction 289 290 /* 291 ** 17500, "Foreign key dropped." 292 */ 293 exec sp_getmessage 17500, @msg output 294 print @msg 295 296 return (0) 297 end 298 299 /* 300 ** Key type must be common so just get rid of the common keys 301 ** with the right ids and depids. Since whenever a common key is defined 302 ** it is added to both of the tables involved, we'll get rid of both of 303 ** those entries. 304 */ 305 delete from syskeys 306 where type = 3 307 and id = object_id(@tabname) 308 and depid = object_id(@deptabname) 309 310 if @@rowcount = 0 311 begin 312 313 /* This error does not use raiserror because it affects 314 ** the installmaster which would break upgrades. 315 */ 316 317 /* 318 ** 17501, "No common keys exist between the two tables or views supplied." 319 */ 320 exec sp_getmessage 17501, @msg output 321 print @msg 322 return (1) 323 end 324 325 /* 326 ** Now get rid of the inverse common key entries. 327 */ 328 329 /* 330 ** This transaction also writes a log record for replicating the 331 ** invocation of this procedure. If logexec() fails, the transaction 332 ** 333 ** IMPORTANT: The name rs_logexec is significant and is used by 334 ** Replication Server. 335 */ 336 begin transaction rs_logexec 337 338 delete from syskeys 339 where type = 3 340 and id = object_id(@deptabname) 341 and depid = object_id(@tabname) 342 343 /* 344 ** Write the log record to replicate this invocation 345 ** of the stored procedure. 346 */ 347 if (logexec() != 1) 348 begin 349 /* 350 ** 17756, "The execution of the stored procedure '%1!' 351 ** in database '%2!' was aborted because there 352 ** was an error in writing the replication log 353 ** record." 354 */ 355 select @dbname = db_name() 356 raiserror 17756, "sp_dropkey", @dbname 357 358 rollback transaction rs_logexec 359 return (1) 360 end 361 362 commit transaction 363 364 /* 365 ** 17502, "Common keys dropped." 366 */ 367 exec sp_getmessage 17502, @msg output 368 print @msg 369 370 return (0) 371
exec sp_procxmode 'sp_dropkey', 'AnyMode' go Grant Execute on sp_dropkey to public go
| DEPENDENCIES |
| PROCS AND TABLES USED writes table sybsystemprocs..syskeys reads table sybsystemprocs..sysobjects 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) |
