DatabaseProcApplicationCreatedLinks
sybsystemprocssp_droptype  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_droptype"           17540
7     **
8     ** 17540, "The type doesn't exist or you don't own it."
9     ** 17541, "Type is being used. You cannot drop it."
10    ** 17542, "Type has been dropped."
11    ** 17704, "Curread label needs to dominate the database hurdle."
12    ** 17756, "The execution of the stored procedure '%1!' in database
13    **         '%2!' was aborted because there was an error in writing the
14    **         replication log record."
15    ** 17844, "You do not own a user datatype of that name."
16    ** 18076, "Could not set curwrite to object level. Set your maxwrite label correctly."
17    ** 18302, "User '%1!' is not a valid user in the '%2!' database."
18    ** 18331, "Drop failed.  Your curwrite label must match the security label of the type.  Check systypes."
19    ** 18773, "HA_LOG: HA consistency check failure in '%1!' on the companion server '%
20    2!'"
21    ** 18787, "The type with name '%1!' does not exists."
22    */
23    
24    /* 
25    ** IMPORTANT: Please read the following instructions before
26    **   making changes to this stored procedure.
27    **
28    **	To make this stored procedure compatible with High Availability (HA),
29    **	changes to certain system tables must be propagated 
30    **	to the companion server under some conditions.
31    **	The tables include (but are not limited to):
32    **		syslogins, sysservers, sysattributes, systimeranges,
33    **		sysresourcelimits, sysalternates, sysdatabases,
34    **		syslanguages, sysremotelogins, sysloginroles,
35    **		sysalternates (master DB only), systypes (master DB only),
36    **		sysusers (master DB only), sysprotects (master DB only)
37    **	please refer to the HA documentation for detail.
38    **
39    **	Here is what you need to do: 
40    **	For each insert/update/delete statement, add three sections to
41    **	-- start HA transaction prior to the statement
42    **	-- add the statement
43    **	-- add HA synchronization code to propagate the change to the companion
44    **
45    **	For example, if you are adding 
46    **		insert master.dbo.syslogins ......
47    **	the code should look like:
48    **	1. Before that SQL statement:
49    **		
50    **	2. Now, the SQL statement:
51    **		insert master.dbo.syslogins ......
52    **	3. Add a HA synchronization section right after the SQL statement:
53    **		
54    **
55    **	You may need to do similar change for each built-in function you
56    **	want to add.
57    **
58    **	After that, you need to add a separate part at a place where it can not
59    **	be reached by the normal execution path:
60    **	clean_all:
61    **		
62    **		return (1)
63    */
64    
65    create or replace procedure sp_droptype
66        @typename varchar(255) /* the user type to drop */
67    as
68    
69        declare @typeid smallint /* the typeid of the usertype to drop */
70        declare @msg varchar(1024)
71        declare @dbname varchar(255)
72        declare @HA_CERTIFIED tinyint /* Is the SP HA certified ? */
73        declare @retstat int
74        declare @dummy int
75        declare @nullarg char(1)
76        declare @gp_enabled int
77        declare @status int
78        declare @uid int
79    
80    
81        select @HA_CERTIFIED = 0
82    
83    
84    
85        /* check to see if we are using HA specific SP for a HA enabled server */
86        exec @retstat = sp_ha_check_certified 'sp_droptype', @HA_CERTIFIED
87        if (@retstat != 0)
88            return (1)
89    
90        if @@trancount = 0
91        begin
92            set chained off
93        end
94    
95        set transaction isolation level 1
96    
97        /*
98        **  Initialize @typeid so we can tell if we can't find it.
99        */
100       select @typeid = 0
101   
102       /*
103       **  Find the user type with @typename.  It must be a user type (usertype > 99)
104       **  and if granular permissions is not enabled it must be owned by the 
105       **  person (or dbo) or if granular permissions is enabled it may be the owner, 
106       **  or a person with 'manage database' permission running the procedure.
107       */
108   
109       select @dbname = db_name()
110       select @nullarg = NULL
111   
112       execute @status = sp_aux_checkroleperm "dbo",
113           "manage database", @dbname, @gp_enabled output
114   
115       if (@status = 0)
116           select @uid = NULL
117       else
118           select @uid = user_id()
119   
120       select @typeid = usertype
121       from systypes
122       where uid = isnull(@uid, uid)
123           and name = @typename
124           and usertype > 99
125   
126       if @typeid = 0
127       begin
128           /*
129           ** 17540, "The type doesn't exist or you don't own it."
130           */
131           raiserror 17540
132           return (1)
133       end
134   
135       /*
136       **  Check to see if the type is being used.  If it is, it can't be dropped.
137       */
138       if exists (select *
139               from syscolumns
140               where usertype = @typeid)
141       begin
142           /*
143           ** 17541, "Type is being used. You cannot drop it."
144           */
145           raiserror 17541
146   
147           /*
148           **  Show where it's being used.
149           */
150           select object = o.name, type = o.type, owner = u.name, column = c.name,
151               datatype = t.name
152           from syscolumns c, systypes t, sysusers u, sysobjects o
153           where c.usertype = @typeid
154               and t.usertype = @typeid
155               and o.uid = u.uid
156               and c.id = o.id
157           order by object, column
158   
159           return (1)
160       end
161   
162   
163   
164   out_of_HA_checking:
165   
166       /*
167       **  Everything is consistent so drop the type.
168       */
169   
170       /* 
171       ** This transaction also writes a log record for replicating the
172       ** invocation of this procedure. If logexec() fails, the transaction	
173       ** is aborted.
174       **
175       ** IMPORTANT: The name rs_logexec is significant and is used by
176       ** Replication Server.
177       */
178       begin transaction rs_logexec
179   
180   
181   
182       delete from systypes
183       where usertype = @typeid
184   
185       /*
186       ** Write the log record to replicate this invocation 
187       ** of the stored procedure.
188       */
189       if (logexec() != 1)
190       begin
191           /*
192           ** 17756, "The execution of the stored procedure '%1!'
193           ** 	   in database '%2!' was aborted because there
194           ** 	   was an error in writing the replication log
195           **	   record."
196           */
197           select @dbname = db_name()
198           raiserror 17756, "sp_droptype", @dbname
199   
200           rollback transaction rs_logexec
201           return (1)
202       end
203   
204   
205   
206       commit transaction rs_logexec
207   
208       /*
209       ** 17542, "Type has been dropped."
210       */
211       exec sp_getmessage 17542, @msg output
212       print @msg
213   
214       return (0)
215   
216   clean_all:
217       rollback transaction rs_logexec
218       return (1)
219   


exec sp_procxmode 'sp_droptype', 'AnyMode'
go

Grant Execute on sp_droptype to public
go
RESULT SETS
sp_droptype_rset_001

DEFECTS
 MURC 6 Unreachable Code 216
 MURC 6 Unreachable Code 217
 MURC 6 Unreachable Code 218
 QJWI 5 Join or Sarg Without Index 140
 QCAS 4 Cartesian product with single row between tables sybsystemprocs..syscolumns c and [sybsystemprocs..systypes t], 1 tables with rc=1 150
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 124
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 156
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 156
 MGTP 3 Grant to public sybsystemprocs..sp_droptype  
 MGTP 3 Grant to public sybsystemprocs..syscolumns  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MGTP 3 Grant to public sybsystemprocs..systypes  
 MGTP 3 Grant to public sybsystemprocs..sysusers  
 MNER 3 No Error Check should check @@error after delete 182
 MNER 3 No Error Check should check return value of exec 211
 MUCO 3 Useless Code Useless Brackets 87
 MUCO 3 Useless Code Useless Brackets 88
 MUCO 3 Useless Code Useless Brackets 115
 MUCO 3 Useless Code Useless Brackets 132
 MUCO 3 Useless Code Useless Brackets 159
 MUCO 3 Useless Code Useless Brackets 189
 MUCO 3 Useless Code Useless Brackets 201
 MUCO 3 Useless Code Useless Brackets 214
 MUCO 3 Useless Code Useless Brackets 218
 QCRS 3 Conditional Result Set 150
 QISO 3 Set isolation level 95
 QNAJ 3 Not using ANSI Inner Join 152
 VNRD 3 Variable is not read @nullarg 110
 VNRD 3 Variable is not read @gp_enabled 113
 VUNU 3 Variable is not used @dummy 74
 MRST 2 Result Set Marker 150
 MSUB 2 Subquery Marker 138
 MTR1 2 Metrics: Comments Ratio Comments: 65% 65
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 7 = 9dec - 4exi + 2 65
 MTR3 2 Metrics: Query Complexity Complexity: 70 65
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, o=sybsystemprocs..sysobjects, u=sybsystemprocs..sysusers} 0 150

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..sysusers  
read_writes table sybsystemprocs..systypes  
reads table sybsystemprocs..sysobjects  
calls proc sybsystemprocs..sp_getmessage  
   reads table sybsystemprocs..sysusermessages  
   reads table master..sysmessages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..syslanguages (1)  
calls proc sybsystemprocs..sp_ha_check_certified  
   reads table tempdb..sysobjects (1)  
writes table sybsystemprocs..sp_droptype_rset_001 
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)  
reads table sybsystemprocs..syscolumns