create procedure sp_droptype --- 1996/04/08 00:00
@typename sysname /* the user type to drop */
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
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
** 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)
** 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]
** 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
return (0) -- sp_droptype