DatabaseProcApplicationCreatedLinks
sybsystemprocssp_droptype  31 Aug 14Defects 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 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    
75    
76        select @HA_CERTIFIED = 0
77    
78    
79    
80        /* check to see if we are using HA specific SP for a HA enabled server */
81        exec @retstat = sp_ha_check_certified 'sp_droptype', @HA_CERTIFIED
82        if (@retstat != 0)
83            return (1)
84    
85        if @@trancount = 0
86        begin
87            set chained off
88        end
89    
90        set transaction isolation level 1
91    
92        /*
93        **  Initialize @typeid so we can tell if we can't find it.
94        */
95        select @typeid = 0
96    
97        /*
98        **  Find the user type with @typename.  It must be a user type (usertype > 99)
99        **  and it must be owned by the person (or dbo) running the procedure.
100       */
101       select @typeid = usertype
102       from systypes
103       where (uid = user_id() or user_id() = 1)
104           and name = @typename
105           and usertype > 99
106   
107       if @typeid = 0
108       begin
109           /*
110           ** 17540, "The type doesn't exist or you don't own it."
111           */
112           raiserror 17540
113           return (1)
114       end
115   
116       /*
117       **  Check to see if the type is being used.  If it is, it can't be dropped.
118       */
119       if exists (select *
120               from syscolumns
121               where usertype = @typeid)
122       begin
123           /*
124           ** 17541, "Type is being used. You cannot drop it."
125           */
126           raiserror 17541
127   
128           /*
129           **  Show where it's being used.
130           */
131           select object = o.name, type = o.type, owner = u.name, column = c.name,
132               datatype = t.name
133           from syscolumns c, systypes t, sysusers u, sysobjects o
134           where c.usertype = @typeid
135               and t.usertype = @typeid
136               and o.uid = u.uid
137               and c.id = o.id
138           order by object, column
139   
140           return (1)
141       end
142   
143   
144   
145   out_of_HA_checking:
146   
147       /*
148       **  Everything is consistent so drop the type.
149       */
150   
151       /* 
152       ** This transaction also writes a log record for replicating the
153       ** invocation of this procedure. If logexec() fails, the transaction	
154       ** is aborted.
155       **
156       ** IMPORTANT: The name rs_logexec is significant and is used by
157       ** Replication Server.
158       */
159       begin transaction rs_logexec
160   
161   
162   
163       delete from systypes
164       where usertype = @typeid
165   
166       /*
167       ** Write the log record to replicate this invocation 
168       ** of the stored procedure.
169       */
170       if (logexec() != 1)
171       begin
172           /*
173           ** 17756, "The execution of the stored procedure '%1!'
174           ** 	   in database '%2!' was aborted because there
175           ** 	   was an error in writing the replication log
176           **	   record."
177           */
178           select @dbname = db_name()
179           raiserror 17756, "sp_droptype", @dbname
180   
181           rollback transaction rs_logexec
182           return (1)
183       end
184   
185   
186   
187       commit transaction rs_logexec
188   
189       /*
190       ** 17542, "Type has been dropped."
191       */
192       exec sp_getmessage 17542, @msg output
193       print @msg
194   
195       return (0)
196   
197   clean_all:
198       rollback transaction rs_logexec
199       return (1)
200   


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 197
 MURC 6 Unreachable Code 198
 MURC 6 Unreachable Code 199
 QJWI 5 Join or Sarg Without Index 121
 QCAS 4 Cartesian product with single row between tables sybsystemprocs..syscolumns c and [sybsystemprocs..systypes t], 1 tables with rc=1 131
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 105
 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 163
 MNER 3 No Error Check should check return value of exec 192
 MUCO 3 Useless Code Useless Brackets 82
 MUCO 3 Useless Code Useless Brackets 83
 MUCO 3 Useless Code Useless Brackets 113
 MUCO 3 Useless Code Useless Brackets 140
 MUCO 3 Useless Code Useless Brackets 170
 MUCO 3 Useless Code Useless Brackets 182
 MUCO 3 Useless Code Useless Brackets 195
 MUCO 3 Useless Code Useless Brackets 199
 QCRS 3 Conditional Result Set 131
 QISO 3 Set isolation level 90
 QNAJ 3 Not using ANSI Inner Join 133
 QPNC 3 No column in condition 103
 MRST 2 Result Set Marker 131
 MSUB 2 Subquery Marker 119
 MTR1 2 Metrics: Comments Ratio Comments: 68% 65
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 6 = 8dec - 4exi + 2 65
 MTR3 2 Metrics: Query Complexity Complexity: 59 65
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, o=sybsystemprocs..sysobjects, u=sybsystemprocs..sysusers} 0 131

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