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

Question

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

对于用户定义的表类型，应该传递枚举中的哪个 type 参数？可以在此处找到相关信息。

像用户定义表一样使用 N'U' 是不起作用的，即 IF OBJECT_ID(N'MyType', N'U') IS NOT NULL

- abatishchev

Answer 1

您可以查看sys.types或使用TYPE_ID：

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

仅作为预防措施：使用type_id不会验证该类型是否为表格类型 - 只是存在该名称的类型。否则，gbn的查询可能更好。

- user121301

---

我天真地尝试使用IF OBJECT_ID(N'MyType', 'TT') IS NULL，但没有成功，但是您的解决方案起作用了。 - Allon Guralnek

“TT” 枚举类型仅适用于 SQL Server 2012 或更高版本（正如我刚刚发现的）。 - Iain

实际上，它仍然无法使用 OBJECT_ID 按名称搜索表类型 -- 请查看 SELECT name FROM sys.objects WHERE type = 'TT'。 - NReilingh

"MyType" 前面加上 "N" 的作用是什么？ - Nishant Kumar

@NishantKumar N前缀使字符串字面值成为Unicode字符串。 - undefined

Answer 2

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

sys.types …它们不在 sys.objects 下使用正常的名称

更新，2013 年 3 月

你还可以使用 TYPE_ID

- gbn

---

我认为你的第二条评论不准确。如果我没错的话，用户定义类型的确是模式作用域的（“Schema_ID”实际上是你提供的sys.types表中的一个属性；这就是为什么它们可以被引用为[dbo].[myUDType]）。然而，你是正确的，UD类型不在sys.objects列表中，因此无法通过OBJECT_ID()访问。（出于某种原因，sys.objects不是一个完整的模式作用域对象列表。） - kmote

@kmote - 它们不直接列在sys.objects中，但每个都有一行。 - Martin Smith

Answer 3

IF EXISTS(SELECT 1 FROM sys.types WHERE name = 'Person' AND is_table_type = 1 AND schema_id = SCHEMA_ID('VAB'))
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)
);

- Tom Groszko

---

我认为这是一个更完整的答案，因为它还检查了模式。 - Hamid Heydarian

schema_id 的值从哪里来的？（因为它不在 OP 中） - Jesse Chisholm

Answer 4

以下示例对我有效，请注意，“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

- wu liang

Answer 5

你可以使用系统表table_types视图。

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

- Maciej Zawiasa