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

score 237 · Accepted Answer · 2010-03-22T20:03:07.317

237

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.

edited Mar 22 '10 at 20:03

answered Mar 22 '10 at 19:49

2

I was naively trying to do `IF OBJECT_ID(N'MyType', 'TT') IS NULL` with no success, but your solution worked. – Allon Guralnek Apr 18 '13 at 07:23
1

The 'TT' enumeration only works in sql server 2012 or later (as i just found out) – Iain Jul 29 '15 at 01:34
4

@Iain It still doesn't, actually. You can't use OBJECT_ID to search for a table type by name -- check out `SELECT name FROM sys.objects WHERE type = 'TT'` – NReilingh Nov 29 '15 at 09:17
what is the use of N before 'MyType'? – Nishant Kumar Mar 15 '22 at 06:30

score 137 · Answer 2 · edited May 30 '23 at 22:38

137

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

sys.types... they aren't in sys.objects under their normal name

Update, Mar 2013

You can use TYPE_ID too

edited May 30 '23 at 22:38

Charlieface

52,284
6
19
43

answered Mar 22 '10 at 19:52

gbn

422,506
82
585
676

6

I believe your second comment is inaccurate. If I'm not mistaken, User-Defined Types are indeed schema-scoped (The `Schema_ID` is in fact one of the attributes in the [sys.types](http://msdn.microsoft.com/en-us/library/ms188021.aspx) table you linked to; this is why they can be referenced as [dbo].[myUDType]). Nevertheless, you are correct that UD types are not listed in sys.objects, and therefore not accessible by OBJECT_ID(). (For whatever reason, sys.objects isn't an exhaustive list of schema-scoped objects.) – kmote Jun 21 '12 at 17:09
1

@kmote - They are not listed in `sys.objects` directly but [there is a row there for each of these](http://stackoverflow.com/a/20483910/73226) – Martin Smith Dec 10 '13 at 00:47

score 21 · Answer 3 · edited Sep 05 '22 at 22:04

21

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

edited Sep 05 '22 at 22:04

user44

682
7
20

answered Mar 22 '10 at 20:01

Tom Groszko

211
1
2

I think this is a more complete answer since it checks schema as well. – Hamid Heydarian May 28 '18 at 10:20
Where does the value for `schema_id` come from? (since it isn't in the OP) – Jesse Chisholm Jan 15 '21 at 19:18

score 7 · Answer 4 · answered Jul 02 '13 at 02:00

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

score 6 · Answer 5 · edited Oct 07 '15 at 11:39

6

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

edited Oct 07 '15 at 11:39

Pரதீப்

91,748
19
131
172

answered Aug 14 '15 at 10:09

Maciej Zawiasa

189
2
6

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

5 Answers5

Linked

Related