SQL Server Table Types In sys.objects

Just follow up on something that was mentioned in the Finding Object Dependencies post, where I observed some oddities with the sys.objects records for user-defined tables types.
In this case, my table type was created in the default ‘dbo’ schema. When querying sys.objects:
select * from sys.objects where type = ‘TT’

A record is returned for the table type, but with a couple of values I wasn’t expecting. The table type name is returned with a ‘TT_’ prefix as well as an eight character code appended to the end. The ‘is_ms_shipped’ value is true, which I wouldn’t expect with a user-created object. Also, the schema ID links to the ‘sys’ schema.
Selecting from the table types view:
select * from sys.table_types

I get the results I would expect, the correct name, schema, etc.
I’ve seen other posts that have noticed the same things, but I’m not able to find any sort of explanation for this behavior.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: