Clever Geek Handbook

T-SQL DROP TYPE IF EXISTS

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

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

I did some research on the internet and found a solution which unfortunately doesn't 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 drop it in SQL Server 2014?

+11


source to share


3 answers


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
    );
+30


source


Use TYPE_ID



Or sys.table_types request

+1


source


try it

IF EXISTS (SELECT 1 FROM sys.types WHERE is_table_type = 1 AND name ='tProjectType') 
    Begin
         DROP TYPE [tProjectType];
         CREATE TYPE [tProjectType] AS TABLE
            (
                 Id INT
                , IsPrivate BIT
                , IsPublic BIT
            );
        END

Before the table type Droping checks that the table type is used in any stored procedures, otherwise it will throw an error like in the table. Type has dependencies

+1


source






More articles:


All Articles