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

Problem

You got the following error message when you tried to delete a user defined data type in SQL Server. You also got a 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

You need to find out all dependent objects and change them. Here is the sql script.

        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'    
    

Navigator

Express SQL Server

Standard SQL Server