T-SQL DROP TYPE IF EXISTS [重复]

This question already has an answer here:

这个问题在这里已有答案:

How to check existence of user-define table type in SQL Server 2008? 5 answers

如何在SQL Server 2008中检查用户定义表类型的存在? 5个答案

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

我目前正在使用SQL Server 2014中的T-SQL脚本。

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.

“MySchema.tProjectType”类型已存在,或者您无权创建它。

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

在SQL Server 2014中删除之前,您是否知道如何成功检查用户定义的表类型是否存在?

Thanks!

3 个解决方案

#1

Please try this, use type_id instead of object_id

请尝试这个,使用type_id而不是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
    );

#2

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

在Droping表类型之前检查表类型是否在任何存储过程中使用,否则会引发错误,如表类型具有依赖性

#3

Use TYPE_ID

Or query sys.table_types

或者查询sys.table_types

#1