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

I have a user-defined table type. I want to check it's existence before editing in a patch using OBJECT_ID(name, type) function.

我有一个用户定义的表类型。在使用OBJECT_ID(名称、类型)函数编辑补丁之前，我想检查它是否存在。

What type from the enumeration should be passed for user-defined table types?

对于用户定义的表类型，应该从枚举中传递什么类型?

N'U' like for user defined table doesn't work, i.e. IF OBJECT_ID(N'MyType', N'U') IS NOT NULL

对于用户定义的表，N' like不能工作，例如，如果OBJECT_ID(N' mytype '， N' u ')不是NULL

5 个解决方案

#1

141

You can look in sys.types or use TYPE_ID:

你可以看看系统。类型或使用TYPE_ID:

IF TYPE_ID(N'MyType') IS NULL ...

Just a precaution: using type_id won't verify that the type is a table type--just that a type by that name exists. Otherwise gbn's query is probably better.

一个注意事项:使用type_id不能验证该类型是一个表类型——只是通过该名称存在一个类型。否则gbn的查询可能更好。

#2

IF EXISTS (SELECT * FROM sys.types WHERE is_table_type = 1 AND name = 'MyType')
    --stuff

sys.types... they aren't schema-scoped objects so won't be in sys.objects

sys.types……它们不是模式作用域的对象，所以不会在sys.objects中

Update, Mar 2013

2013年3月更新

You can use TYPE_ID too

也可以使用TYPE_ID

#3

IF EXISTS(SELECT 1 FROM sys.types WHERE name = 'Person' AND is_table_type = 1 AND SCHEMA_ID('VAB') = schema_id)
DROP TYPE VAB.Person;
go
CREATE TYPE VAB.Person AS TABLE
(    PersonID               INT
    ,FirstName              VARCHAR(255)
    ,MiddleName             VARCHAR(255)
    ,LastName               VARCHAR(255)
    ,PreferredName          VARCHAR(255)
);

#4

Following examples work for me, please note "is_user_defined" NOT "is_table_type"

下面的示例为我工作，请注意“is_user_defined”而不是“is_table_type”

IF TYPE_ID(N'idType') IS NULL
CREATE TYPE [dbo].[idType] FROM Bigint NOT NULL
go

IF not EXISTS (SELECT * FROM sys.types WHERE is_user_defined = 1 AND name = 'idType')
CREATE TYPE [dbo].[idType] FROM Bigint NOT NULL
go

#5

You can use also system table_types view

还可以使用system table_types视图

IF EXISTS (SELECT *
           FROM   [sys].[table_types]
           WHERE  user_type_id = Type_id(N'[dbo].[UdTableType]'))
  BEGIN
      PRINT 'EXISTS'
  END

#1

141