sql-server - 如何使用 SQL 查询在 SQL Server 2008 R2 中获取用户定义的类型定义？

Question

我创建了一个示例 java 程序，在其中使用以下查询获得所有对象的定义，如 View 、触发器、函数等:

select object_definition(object_id) 
from sys.objects 
where type = 'V'; //for Views

select object_definition(object_id) 
from sys.objects 
where type = 'TF'; //for Functions

select object_definition(object_id) 
from sys.objects 
where type = 'TR'; //for triggers

但是 sys.objects不包含用户定义的数据类型和用户定义的表类型。我正在使用以下查询来获取用户定义的类型:-

select * 
from sys.types 
where is_user_defined = 1;

是否有任何 sql 查询可以用来获取用户定义的数据类型和用户定义的表类型的定义？

Answer 1

用户定义的类型本身不会有对象定义——它是一个类型而不是一个对象。

对于用户定义的表类型，您可以从 sys.table_types 获取信息。但你不会得到 object_definition来自 type_table_object_id这比你从 object_id sys.objects 中的用户表.您可能想查看此链接以创建表创建脚本:

Generate Create Table Script

对于您的 UDT，您必须遵循类似的路径并编写一些内容来查询系统表以获取有关类型的信息，然后相应地构建 SQL 字符串。您需要像运行 SQL 一样(基于编写类型创建脚本时的服务器跟踪):

exec sp_executesql N'SELECT
st.name AS [Name],
sst.name AS [Schema],
ISNULL(s1st.name, N'''') AS [Owner],
CAST(case when st.principal_id is null then 1 else 0 end AS bit) AS     [IsSchemaOwned],
st.user_type_id AS [ID],
CAST(CASE WHEN baset.name IN (N''nchar'', N''nvarchar'') AND st.max_length <> -1 THEN st.max_length/2 ELSE st.max_length END AS int) AS [Length],
CAST(st.precision AS int) AS [NumericPrecision],
CAST(st.scale AS int) AS [NumericScale],
CAST(CASE WHEN baset.name IN (N''decimal'', N''int'', N''numeric'', N''smallint'', N''tinyint'', N''bigint'') THEN 1 ELSE 0 END AS bit) AS [AllowIdentity],
st.max_length AS [MaxLength],
st.is_nullable AS [Nullable],
(case when st.default_object_id = 0 then N'''' else def.name end) AS [Default],
(case when st.default_object_id = 0 then N'''' else schema_name(def.schema_id) end) AS [DefaultSchema],
(case when st.rule_object_id = 0 then N'''' else rul.name end) AS [Rule],
(case when st.rule_object_id = 0 then N'''' else schema_name(rul.schema_id) end) AS [RuleSchema],
ISNULL(st.collation_name, N'''') AS [Collation],
CAST(CASE WHEN baset.name IN ( N''varchar'', N''varbinary'', N''nvarchar'' )     THEN 1 ELSE 0 END AS bit) AS [VariableLength],
baset.name AS [SystemType]
FROM
sys.types AS st
INNER JOIN sys.schemas AS sst ON sst.schema_id = st.schema_id
LEFT OUTER JOIN sys.database_principals AS s1st ON s1st.principal_id =     ISNULL(st.principal_id, (TYPEPROPERTY(QUOTENAME(SCHEMA_NAME(st.schema_id)) + ''.'' + QUOTENAME(st.name), ''OwnerId'')))
LEFT OUTER JOIN sys.types AS baset ON (baset.user_type_id = st.system_type_id and baset.user_type_id = baset.system_type_id) or ((baset.system_type_id = st.system_type_id) and (baset.user_type_id = st.user_type_id) and (baset.is_user_defined = 0) and (baset.is_assembly_type = 1)) 
LEFT OUTER JOIN sys.objects AS def ON def.object_id = st.default_object_id
LEFT OUTER JOIN sys.objects AS rul ON rul.object_id = st.rule_object_id
WHERE
(st.schema_id!=4 and st.system_type_id!=240 and st.user_type_id != st.system_type_id and st.is_table_type != 1)and(st.name=@_msparam_0 and sst.name=@_msparam_1)',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000)',@_msparam_0=N't_your_udt',@_msparam_1=N'your_schema_name'