DatabaseProcApplicationCreatedLinks
sybsystemprocssp_dropkey  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/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 or replace 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
DEFECTS
 MCTR 4 Conditional Begin Tran or Commit Tran 165
 MCTR 4 Conditional Begin Tran or Commit Tran 199
 MCTR 4 Conditional Begin Tran or Commit Tran 252
 MCTR 4 Conditional Begin Tran or Commit Tran 288
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 133
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 169
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 255
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 306
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 339
 TNOU 4 Table with no unique index sybsystemprocs..syskeys sybsystemprocs..syskeys
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 101
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 115
 MAW1 3 Warning message on %name% sybsystemprocs..syskeys.id: Warning message on syskeys 132
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 208
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 210
 MAW1 3 Warning message on %name% sybsystemprocs..syskeys.id: Warning message on syskeys 256
 MAW1 3 Warning message on %name% sybsystemprocs..syskeys.id: Warning message on syskeys 307
 MAW1 3 Warning message on %name% sybsystemprocs..syskeys.id: Warning message on syskeys 340
 MGTP 3 Grant to public sybsystemprocs..sp_dropkey  
 MGTP 3 Grant to public sybsystemprocs..syskeys  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MNER 3 No Error Check should check @@error after delete 131
 MNER 3 No Error Check should check return value of exec 149
 MNER 3 No Error Check should check @@error after delete 167
 MNER 3 No Error Check should check return value of exec 176
 MNER 3 No Error Check should check @@error after delete 254
 MNER 3 No Error Check should check return value of exec 293
 MNER 3 No Error Check should check @@error after delete 305
 MNER 3 No Error Check should check return value of exec 320
 MNER 3 No Error Check should check @@error after delete 338
 MNER 3 No Error Check should check return value of exec 367
 MUCO 3 Useless Code Useless Brackets 67
 MUCO 3 Useless Code Useless Brackets 81
 MUCO 3 Useless Code Useless Brackets 92
 MUCO 3 Useless Code Useless Brackets 107
 MUCO 3 Useless Code Useless Brackets 123
 MUCO 3 Useless Code Useless Brackets 141
 MUCO 3 Useless Code Useless Brackets 184
 MUCO 3 Useless Code Useless Brackets 196
 MUCO 3 Useless Code Useless Brackets 201
 MUCO 3 Useless Code Useless Brackets 221
 MUCO 3 Useless Code Useless Brackets 231
 MUCO 3 Useless Code Useless Brackets 266
 MUCO 3 Useless Code Useless Brackets 273
 MUCO 3 Useless Code Useless Brackets 285
 MUCO 3 Useless Code Useless Brackets 296
 MUCO 3 Useless Code Useless Brackets 322
 MUCO 3 Useless Code Useless Brackets 347
 MUCO 3 Useless Code Useless Brackets 359
 MUCO 3 Useless Code Useless Brackets 370
 MUOT 3 Updates outside transaction 305
 QISO 3 Set isolation level 52
 MSUB 2 Subquery Marker 99
 MSUB 2 Subquery Marker 208
 MTR1 2 Metrics: Comments Ratio Comments: 62% 36
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 6 = 20dec - 16exi + 2 36
 MTR3 2 Metrics: Query Complexity Complexity: 126 36

DEPENDENCIES
PROCS AND TABLES USED
writes table sybsystemprocs..syskeys  
reads table sybsystemprocs..sysobjects  
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)