I'm creating User-Defined Table Types on SQL Server 2008R2/2012 with this statement:
CREATE TYPE [MySchemaName].[MyUserTableType]
As Table ( [Id] varchar(20) NOT NULL );
This works as intended.
I also got a stored procedure which deletes all associated object to a given Schema. This is where my User-Defined Table Types are not working properly. I am expecting that those Types are created with an associated schema.
If i try to drop the schema, it will complain that the schema is associated with my User-Defined Table Type. Hence it will not be deleted.
But if i query all objects in my DB which are TYPE_TABLE it tells me that my Table types do not belong to my schema. They belong to the schema 'sys'
SELECT name, schema_id, type_desc
FROM [TESTDB].SYS.OBJECTS
WHERE type_desc = 'TYPE_TABLE';
SELECT * FROM sys.schemas;
Any idea why?
Here is a screenshot from my output
Instead of looking in sys.objects
for these you should look in sys.types
or sys.table_types
(which additionally exposes the type_table_object_id
).
SELECT name,
schema_id /*Will be the "test" schema id*/
FROM sys.types
WHERE is_table_type = 1
AND name = 'MyUserTableType'
When you create a user defined type it adds a row to sys.sysscalartypes
with the user supplied schema and name and a row to sys.sysschobjs
with a system generated name in the sys
schema. The system generated name is created by concatenating TT_
+ FriendlyName + _
+ Hex version of object id.
The two entities are related together via sys.syssingleobjrefs
/*This query only works via the DAC*/
SELECT so.id AS object_id,
st.id AS user_type_id,
*
FROM sys.sysschobjs so
JOIN sys.syssingleobjrefs sor
ON sor.indepid = so.id
JOIN sys.sysscalartypes st
ON st.id = sor.depid
WHERE st.name = 'MyUserTableType'