« Video: Restoring Databases with SQL Server Management Studio | Main | New Video: Restoring SQL Server 2000 Databases »

Bug with User-Defined-Table-Types and is_ms_shipped in SQL 2008?

Sure looks like a bug to me.

Create the following:

CREATE TYPE dbo.LocationPreferences AS TABLE (
	Latitude decimal(9,6), 
	Longitude decimal(9,6),
	MinPref int,
	MaxPref int,
	Importance int

Then try to pull it back in a query from sys.objects listing everything that isn't shipped by MS (i.e. a list of what you would expect to be nothing but user-created objects):

SELECT name,object_id, type_desc 
FROM sys.objects
WHERE is_ms_shipped = 0

And.... your UDTT doesn't come back in the result set.

But fire off the following:

SELECT name,object_id, type_desc 
FROM sys.objects
WHERE name NOT LIKE 'sys%'

And you get results like this:



Which would seem to indicate:
a) That there's a bug/issue with is_ms_shipped when it comes to UDTTs
b) That there's some weirdness going on as well (notice the names of my UDTT objects compared to normal object names).


Loading Comments... loading comments

Post a comment

Comments may be moderated.

The following pseudo-markup is permitted:
      bold : *strong*
      italic : _em_
      hyperlinks : [linktext|http://link.url.here]