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

sql sql-server sql-server-2008 tsql user-defined-types
abatishchev picture abatishchev · Mar 22, 2010 · Viewed 108.5k times · Source

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

user121301 picture user121301 · Mar 22, 2010

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.

Related questions

How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More
How can I select the first day of a month in SQL?

I just need to select the first day of the month of a given datetime variable. I know it's quite easy to do using this kind of code: select CAST(CAST(YEAR(@mydate) AS VARCHAR(4)) + '/' + CAST(MONTH(@mydate) …

Read More
SQL Server SELECT INTO @variable?

I have the following code in one of my Sql (2008) Stored Procs which executes perfectly fine: CREATE PROCEDURE [dbo].[Item_AddItem] @CustomerId uniqueidentifier, @Description nvarchar(100), @Type int, @Username nvarchar(100), AS BEGIN DECLARE @TopRelatedItemId uniqueidentifier; SET @TopRelatedItemId = ( SELECT top(1) RelatedItemId FROM …

Read More