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

SqlSql ServerSql Server-2008TsqlUser Defined-Types

Sql Problem Overview


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

Sql Solutions


Solution 1 - Sql

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.

Solution 2 - Sql

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

Update, Mar 2013

You can use TYPE_ID too

Solution 3 - Sql

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

Solution 4 - Sql

Following examples work for me, please note "is_user_defined" NOT "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

Solution 5 - Sql

You can use also system table_types view

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

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionabatishchevView Question on Stackoverflow
Solution 1 - Sqluser121301View Answer on Stackoverflow
Solution 2 - SqlgbnView Answer on Stackoverflow
Solution 3 - SqlTom GroszkoView Answer on Stackoverflow
Solution 4 - Sqlwu liangView Answer on Stackoverflow
Solution 5 - SqlMaciej ZawiasaView Answer on Stackoverflow