1

I've never used a user type in SQLS before, and I've been handed some sql code that creates one. Unfortunately the code assumes it doesn't exist, so I added a IF...DROP. But that does not happen...

IF EXISTS
(
    SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.udtt_Total')
)
BEGIN
     DROP TYPE udtt_Total
END;

CREATE TYPE [dbo].[udtt_Total] AS TABLE(...

It's complaining the type already exists. So I went into sys_objects and found it:

TT_udtt_Total_7D98A078

Can someone explain what's happened here and how to properly DROP this? There's nothing else in the system with a name remotely like any of this, it doesn't seem to be

Thom A
  • 88,727
  • 11
  • 45
  • 75
Maury Markowitz
  • 9,082
  • 11
  • 46
  • 98
  • If you are in one transaction it wont register it is dropped. Try putting a GO after the If check that drops it. – Brad May 30 '23 at 15:21
  • Why not just use `DROP TYPE IF EXISTS`? – Thom A May 30 '23 at 15:25
  • @ThomA - sqls 14. – Maury Markowitz May 30 '23 at 15:26
  • @Brad - no luck. I assume I put the GO after the END;? – Maury Markowitz May 30 '23 at 15:27
  • So again, why not use it? `DROP IF EXISTS` was added in SQL Server 2016 (version 13), and you're using version 14 (2017). – Thom A May 30 '23 at 15:27
  • I am using SQLS 2014. Wait... is 2016 version 14?! God, who thought that was a good idea... – Maury Markowitz May 30 '23 at 15:27
  • 2014 <> 14 @MauryMarkowitz . – Thom A May 30 '23 at 15:27
  • 1
    *"Wait... is 2016 version 14?! God, who thought that was a good idea..."* It makes logical sense, 2017 was the *14th* version of the data engine. The version after version 14 (2019) is version 15, the version prior to version 14 (2016) is version 13. Version numbers going up incrementally makes perfect sense. *(Note, I typoed the release version in my prior comment and has since been corrected.)* – Thom A May 30 '23 at 15:28
  • `OBJECT_ID(N'dbo.udtt_Total')` is null when the type **does** exist – Martin Smith May 30 '23 at 15:35
  • Are you implying that `OBJECT_ID` would return a non-`NULL` value when the `TYPE` *doesn't* exist, @MartinSmith ? Because `OBJECT_ID` returns `NULL` regardless of if it exists or not; `OBJECT_ID` doesn't work for `TYPE`s. – Thom A May 30 '23 at 15:36
  • @ThomA - No, I'm not implying that – Martin Smith May 30 '23 at 15:45
  • True, but the [documentation](https://learn.microsoft.com/en-us/sql/t-sql/functions/object-id-transact-sql) is, in truth, a little wrong here, @MartinSmith . It states *"Objects that are not schema-scoped, such as DDL triggers, cannot be queried by using OBJECT_ID. For objects that are not found in the [sys.objects](https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-objects-transact-sql?view=sql-server-ver16) catalog view, obtain the object identification numbers by querying the appropriate catalog view."* [1] – Thom A May 30 '23 at 15:46
  • Turns out I did look into the `TT` object entry in `sys.objects` before https://stackoverflow.com/a/20483910/73226 – Martin Smith May 30 '23 at 15:48
  • `TYPE`s, however, do appear in `sys.objects` *and* are schema bound. For some reason, however, Microsoft chose to prefix and suffix `TYPE` name's (table type or not), and cause `OBJECT_ID` to not work. [2] – Thom A May 30 '23 at 15:49
  • @ThomA - ok so I'm not just imagining this. It would be OK if you could simply add the TT_ at the front, but it is also adding a hex string at the end and I have no idea what it represents. – Maury Markowitz May 30 '23 at 15:54
  • See the link I already gave above https://stackoverflow.com/a/20483910/73226. The hex is what you see with `SELECT CAST(type_table_object_id as varbinary(10)), * FROM sys.table_types WHERE name = 'udtt_Total'` – Martin Smith May 30 '23 at 15:55
  • No, @MauryMarkowitz , `OBJECT_ID` doesn't work for `TYPE`s. (I'm actually raising an issue against the documentation for this). – Thom A May 30 '23 at 15:55
  • `SELECT OBJECT_ID('sys.TT_udtt_Total_5AEE82B9')` etc. does work but is utterly pointless because the hex string is just the encoded object_id so if you knew that there wouldn't be any point running it – Martin Smith May 30 '23 at 16:04
  • @MartinSmith - yes exactly! Very annoying. – Maury Markowitz May 30 '23 at 16:05
  • But the moral of the story is just to use type functions. I presume the object auto created in the sys schema and visible in `sys.objects` is just there to get an object_id that can then be used in `sys.columns` etc. So it is just an auto created instance of the type - which is distinct from the type itself – Martin Smith May 30 '23 at 16:07
  • I am confused - is there some reason you can't use sys.types instead of trying to use sys.objects and specifying an object id? – Jeff May 30 '23 at 18:40
  • @Jeff - None at all, I was simply unaware this was even an issue I needed to know about. And, IMHO, it *shouldn't* be. – Maury Markowitz May 31 '23 at 11:42

0 Answers0