OBJECT_ID(N'dbo.ori', 'TT') does not return ID for table types - by Vladimir Moldovanenko

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 725828 Comments
Status Closed Workarounds
Type Bug Repros 1
Opened 2/20/2012 2:23:38 PM
Access Restriction Public


See http://msdn.microsoft.com/en-us/library/bb510623.aspx

type_table_object_id = OBJECT_ID(N'dbo.ori', 'TT')

However, null is returned by function, when type is specified.

Sign in to post a comment.
Posted by Microsoft on 2/20/2013 at 11:27 AM

Hi Valdimir. Thanks for your feedback. As noted previously, this is the current behavior and we don't have plans to change this at this time. I understand your frustration but we don't treat table types as objects internally so we are not able to prioritize making changes to object_id function to treat it as an object for the purposes of ID lookup. For your purposes, it is probably faster to do a lookup against sys.objects directly to pull the ID.
Posted by Vladimir Moldovanenko on 2/20/2013 at 10:07 AM
As a user, when I create object, I do not name it "TT_ori_0AFD7C01", I name it tt.ori.
I must not be forced to know your internal object names and your concateration methods to check for object existance using standard function.
As a user, that is what I expect and OBJECT_ID fails this.
Posted by Vladimir Moldovanenko on 2/20/2013 at 10:01 AM
And why would I be trying to resolve and then concatenate some ID number so I can call object_id as you suggest to this ID again? I have to use dynamic SQL for this? This just makes no sense! Is it not the purpose of OBJECT_ID() function to get me the right ID by user name and object type?
And where is this nonsense explained in SQL help?
Posted by Vladimir Moldovanenko on 2/20/2013 at 9:53 AM
Sir, your explanation makes no sense from user point of view.
I an checking for user object called "dbo.ori" which is qualieid by "TT" type (table type), as specified in SQL help, see link provided.
That is is all that i need to specify for this check.

OBJECT_ID needs to figure out how to resolve schema, internal IDs and what not by using type of object specified. it should return ID of the object type "TT" with user specified name.
Posted by Microsoft on 2/20/2013 at 9:42 AM

Sorry, that should actually be "select object_id(N'sys.TT_ori_<number>',N'TT')"

(I missed the extra "sys." in front of the TT_...)
Posted by Microsoft on 2/20/2013 at 9:39 AM

Here's what's happening, table types are not actually treated as objects elsewhere by the system. They are listed there for legacy reasons. Internally, the system creates a different object. You can see this listed in the sys.objects table as "TT_ori_<number>". If you then used "select object_id(N'TT_ori_<number>',N'TT') this should return the correct object ID. Note, in the above, please replace "<number>" with the actual number from the sys.objects table. Again, this behavior is by design as table types are not really considered objects.
Posted by Vladimir Moldovanenko on 2/19/2013 at 5:27 PM
Resolved by Design? what do you mean by that? I am clearly trying to check for table type.
Posted by Microsoft on 2/19/2013 at 4:55 PM
Ah, sorry, I see that you are actually creating a table type. Investigating further.
Posted by Microsoft on 2/19/2013 at 4:34 PM
Hi Vladimir, thank you for your feedback. In this case, I believe you need to use "U" as your object type instead of "TT". "U" references user defined tables. The system was trying to search for a "TT" type table and thus returned null since it couldn't find one. TT actually refers to table types: http://msdn.microsoft.com/en-us/library/bb522526(v=SQL.105).aspx