SQL Server 创建用户定义的表类型,但架构无法正常工作 [英] SQL Server create User-Defined Table Types with schema not working properly
问题描述
我正在使用以下语句在 SQL Server 2008R2/2012 上创建用户定义的表类型:
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 );
这按预期工作.
我还得到了一个存储过程,用于删除给定架构的所有关联对象.这是我的用户定义表类型无法正常工作的地方.我期待这些类型是使用关联的模式创建的.
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.
但是如果我查询数据库中所有类型为 TYPE_TABLE 的对象,它会告诉我我的表类型不属于我的架构.它们属于架构sys"
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;
知道为什么吗?
这是我输出的截图
推荐答案
与其在 sys.objects
中查找,不如在 sys.types
或 中查找>sys.table_types
(另外公开了 type_table_object_id
).
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'
当您创建用户定义的类型时,它会向 sys.sysscalartypes
添加一行,其中包含用户提供的架构和名称,并向 sys.sysschobjs
添加一行,并生成系统sys
架构中的名称.系统生成的名称是通过连接 TT_
+ FriendlyName + _
+ 对象 ID 的十六进制版本来创建的.
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.
这两个实体通过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'
这篇关于SQL Server 创建用户定义的表类型,但架构无法正常工作的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!