Database | Proc | Application | Created | Links |
sybsystemprocs | sp_droptype ![]() | ![]() | 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_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 |
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) ![]() |