T-SQL DROP TYPE IF EXISTS [duplicate]

Question

I'm currently working on a script in T-SQL in SQL Server 2014.

I need to drop a user-defined table type, but only if it exists, and create it again after the delete/drop type.

I did some research on the web and found a solution, which does, unfortunately, not work at all.

My current script looks like this:

IF OBJECT_ID('MySchema.tProjectType', 'U') IS NOT NULL
        DROP TYPE [MySchema].[tProjectType];

CREATE TYPE [MySchema].[tProjectType] AS TABLE
    (
        Id INT
        , IsPrivate BIT
        , IsPublic BIT
    );

My error message:

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

Do you know how to successfully check if a user defined table type exists before I can delete it in SQL Server 2014?

Answer 1

Please try this, use type_id instead of object_id

IF type_id('[MySchema].[tProjectType]') IS NOT NULL
        DROP TYPE [MySchema].[tProjectType];


CREATE TYPE [MySchema].[tProjectType] AS TABLE
    (
        Id INT
        , IsPrivate BIT
        , IsPublic BIT
    );