T-SQL DROP TYPE IF EXISTS

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?

Table types aren't objects -- specifically, they aren't *tables*. Use `TYPE_ID` instead. — Jeroen Mostert, Jul 13 '17 at 14:05

score 61 · Accepted Answer · answered Jul 13 '17 at 14:05

61

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

answered Jul 13 '17 at 14:05

Ronaldo Cano

905
8
19

5

SQL Server 2016 SP1 onward, you can now simply use DROP TYPE IF EXISTS ... according to this article https://learn.microsoft.com/en-us/sql/t-sql/statements/drop-type-transact-sql?view=sql-server-2017 – Vincy Dec 01 '18 at 15:32

gbn · Answer 2 · 2017-07-13T14:10:17.830

2

Use TYPE_ID

Or query sys.table_types

edited Jul 13 '17 at 14:10

answered Jul 13 '17 at 14:01

gbn

422,506
82
585
676

1

`OBJECT_ID` will not work. There is a `TT` object backing every table type, but it has a system-generated name. – Jeroen Mostert Jul 13 '17 at 14:07
@JeroenMostert I thought they'd fixed this since https://stackoverflow.com/a/2495240/27535 – gbn Jul 13 '17 at 14:11
Verified explicitly that this does not work on SQL Server 2016 SP1 CU. Neither `OBJECT_ID('x')` nor `OBJECT_ID('x', 'TT')` will return results for a table type `x`. The backing internal table will have a name like `TT_x_12345678`. I don't think you're really supposed to do anything with it; it's there for completeness (along with any constraints), but the table isn't really the type. – Jeroen Mostert Jul 13 '17 at 14:13

score 1 · Answer 3 · answered Jul 13 '17 at 14:04

Try this

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 Droping table type check that table type is using in any stored procedures otherwise it will raise error like table Type is having dependencies

T-SQL DROP TYPE IF EXISTS

3 Answers3