Cannot delete SQL Server User Defined Data Type; Cannot drop type 'dbo.xxx' because it is currently in use.

Problem

The following error message appear when you tried to delete a user defined data type in SQL Server. You also receive an empty list when you view the dependencies.

        Msg 3732, Level 16, State 1, Line 3
        Cannot drop type 'dbo.xxx' because it is currently in use.
    

Solution

Locate all dependent objects and change them to the SQL script as listed below:

        SELECT o.name as 'Table', c.name as 'Column' 
        FROM sys.columns c
        Join sys.objects o On o.object_id = c.object_id
        Join sys.types t On t.user_type_id = c.user_type_id
        Where t.name = 'your_type'    
    

Add Feedback