Cannot drop type

Cannot drop a type event though it is not in use.

The type was created via create type and is an non-nullable nvarchar. It should have been nullable.

I have removed all dependencies on the type, by dropping tabes, stored procs etc, but still cannot drop it. SSMSE says the type cannot be dropped because it's in use. SSMSE shows no dependencies on the type.

Any help?

Can you run these queries

select object_name(object_id), * from sys.columns where user_type_id=type_id('Your_type_name')

select object_name(object_id), * from sys.parameters where user_type_id=type_id('Your_type_name')

and see if they return any rows back. If they do then there are still some dependencies left in the database on the type. The object_id is the object which has those dependencies.

Thanks

Asvin

|||

Many thanks Asvin, that got it.

Turned out an old view was still referencing the type. Strange that SSMSE did not see this dependency.

|||

Also, check out this very good blog by Umachandar titled "Direct Dependencies on column"

http://blogs.msdn.com/sqltips/archive/2005/07/05/435882.aspx