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

Question

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.

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

Answer 1

Is it possible to use OBJECT_ID() or I have to query system table:

IF EXISTS(SELECT 1 FROM sys.types WHERE name = 'MyType')
   DROP TYPE dbo.MyType

Answer 2

Try associating a schema with your table name, i.e. dbo.

Answer 3

You can look in sys.types or use 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.

Answer 4

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

sys.types... they aren't schemabound objects so won't be in sys.objects

Answer 5

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