Please use FireFox to view this page

create procedure sp_droptype --- 1996/04/08 00:00 @typename sysname /* the user type to drop */ as declare @typeid smallint /* the typeid of the usertype to drop */ /* ** Initialize @typeid so we can tell if we can't find it. */ select @typeid = 0 /* ** Find the user type with @typename. It must be a user type (xusertype > 256) ** and it must be owned by the person (or special role) running the procedure. */ select @typeid = xusertype from systypes where name = @typename and xusertype > 256 AND (is_member('db_owner') = 1 OR is_member('db_ddladmin') = 1 OR is_member(user_name(uid))=1) if @typeid = 0 begin raiserror(15105,-1,-1) return (1) end /* ** Check to see if the type is being used. If it is, it can't be dropped. */ if exists (select * from syscolumns where xusertype = @typeid) begin raiserror(15180,-1,-1) /* ** Show where it's being used. */ select object = o.name, type = o.xtype, owner = u.name, [column] = c.name, datatype = t.name from syscolumns c, systypes t, sysusers u, sysobjects o where c.xusertype = @typeid and t.xusertype = @typeid and o.uid = u.uid and c.id = o.id order by object, [column] return (1) end /* ** Everything is consistent so drop the type. */ delete from systypes where xusertype = @typeid delete from sysproperties where type = 1 and id = 0 and smallid = @typeid raiserror(15467,-1,-1) return (0) -- sp_droptype