Dropping / recreating type in SQL Server

Question

IF OBJECT_ID('[dbo].[Order]') IS NOT NULL
    DROP TYPE [dbo].[Order]
GO

CREATE TYPE [dbo].[Order] AS TABLE 
(
    [Id] INT NULL,
    [Order] INT NULL
);

This code doesn't drop the table. I get this error:

The type 'dbo.Order' already exists, or you do not have permission to create it.

How I can drop/recreate type in SQL Server?

Answer 1

Any of your stored procedure may be using this table type. That's why it doesn't allow you to drop. You may be getting following error.

Msg 3732, Level 16, State 1, Line 4 Cannot drop type 'tabletype' because it is being referenced by object 'storedprocedurename'. There may be other objects that reference this type.

Msg 219, Level 16, State 1, Line 3 The type 'tabletype' already exists, or you do not have permission to create it.

check that storedprocedure or function name. just comment the usage for a while and once reference is removed. you can drop and recreate the table type. after that uncomment the reference usage in stored procedure / function.

This will surely works !!