How to check existence of user-define table type in 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.
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
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.
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
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)
);
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