寻找用户定义类型的SQL基础类型

Question

我正在处理一个遗留数据库，其中有大量用户定义的SQL类型。我正在使用.NET编写一个方法，在SqlParameter对象中定义参数。为了正确定义这些参数并在运行时动态创建它们，我需要用户定义类型的底层SQL类型。

为此，我创建了以下过程：

(@typename sysname)  

AS  

SET NOCOUNT ON  

SELECT distinct
st.name as UserType,
t.precision, t.max_length,
bt.name as BaseType
FROM
dbo.syscolumns c
INNER JOIN dbo.systypes st ON st.xusertype = c.xusertype
INNER JOIN dbo.systypes bt ON bt.xusertype = c.xtype
inner join sys.types t on st.name = t.name
WHERE
st.name = 'bVendor'

我想知道获取用户定义类型的基础类型是否是最佳选择？

- Aaron L.

---

这个方法看起来很合理。你有什么特别的问题吗？ - John Dewey

我的唯一问题是这个过程中的输入类型是sysname，这似乎是一个奇怪的选择作为输入类型。当我传入一个字符串时它似乎可以正常工作，但这让我有点紧张。 - Aaron L.

sysname只是nvarchar的别名，这让你感到紧张的原因是什么？ - Aaron Bertrand

这篇帖子中的答案让我开始质疑是否使用那种类型是一个好主意：链接我想，如果它只是另一种说法，表示nvarchar(128) NOT NULL，那么我就不需要担心了。 - Aaron L.

Answer 1

您不应该使用systypes或syscolumns - 这些是向后兼容视图，而sys.types和sys.columns则更受欢迎，除非您想编写适用于SQL Server 2000+的代码（我也不建议这样做）。

要获取已知名称类型的信息：

SELECT name, precision, scale, max_length
  FROM sys.types AS t
  WHERE name = 'bVendor';

为了获取数据库中所有用户定义类型的信息：

SELECT name, precision, scale, max_length
  FROM sys.types AS t
  WHERE is_user_defined = 1;

获取特定表的所有类型（系统定义和用户定义）的信息：

要包括基础类型，请进行更新：

SELECT 
  [column] = c.name, 
  [base type] = COALESCE(bt.name, t.name),
  [defined type] = t.name, 
  t.precision, 
  t.scale, 
  t.max_length
FROM sys.columns AS c
INNER JOIN sys.types AS t
ON c.system_type_id = t.system_type_id
AND c.user_type_id = t.user_type_id
LEFT OUTER JOIN sys.types AS bt
ON t.is_user_defined = 1
AND bt.is_user_defined = 0
AND t.system_type_id = bt.system_type_id
AND t.user_type_id <> bt.user_type_id
WHERE c.object_id = OBJECT_ID('dbo.your_table_name');

请注意，如果您使用别名类型（例如CREATE TYPE blat FROM nvarchar(32);），则此操作将返回两行。如果您确实必须使用它们（我也不推荐使用它们），那么请将连接子句更改为：

ON t.is_user_defined = 1
AND bt.is_user_defined = 0
AND t.system_type_id = bt.system_type_id
AND bt.user_type_id = bt.system_type_id

- Aaron Bertrand

---

谢谢您的回答。不过我真正需要的是用户定义类型的底层SQL类型。您建议的语句中的名称列仍然返回用户定义类型的名称，而不是它所代表的SQL类型。 - Aaron L.

@AaronL 抱歉，输出中漏掉了。请现在再试一次。 - Aaron Bertrand

我认为这会让我接近我所需要的。谢谢。然而，令我惊讶的是，没有一个表来保存用户定义类型的名称和它们相关的SQL类型的属性。我想知道微软在SSMS中用什么来填充用户定义类型列表？该列表始终包含用户类型及其对应的SQL类型。 - Aaron L.

@AaronL. 你的意思是什么？正如我的查询所示，它们在 sys.types 中... 它们只是在行中不重复名称（这称为规范化）- 它们指向 sys.types 表中的其他行以获取基本类型信息。管理工具代码将使用与我上面编写的相似的连接... 当然，你可以运行 Profiler 来查看它确切地做了什么。 - Aaron Bertrand

啊，我现在明白了。我显然是有些傻。再次感谢您的帮助。我很感激。 - Aaron L.

Answer 2

要获取数据库中所有用户定义类型的信息，可以使用 INFORMATION_SCHEMA.DOMAINS：

SELECT 
   DOMAIN_SCHEMA,
   DOMAIN_NAME,
   DATA_TYPE,
   NUMERIC_PRECISION,
   NUMERIC_SCALE,
   DATETIME_PRECISION,
   CHARACTER_MAXIMUM_LENGTH

FROM 
   INFORMATION_SCHEMA.DOMAINS

更新： 以下查询返回所有用户定义类型的可读性定义：

  SELECT 
     DOMAIN_SCHEMA + '.' + DOMAIN_NAME AS TypeName,
     DATA_TYPE + 
        COALESCE('('+ 
           CAST(COALESCE(NUMERIC_PRECISION,DATETIME_PRECISION,CHARACTER_MAXIMUM_LENGTH) AS VARCHAR) + 
           COALESCE(',' + CAST(NUMERIC_SCALE AS VARCHAR),'') +
        ')','') AS Definition

  FROM 
  (
     SELECT 
        DOMAIN_SCHEMA,
        DOMAIN_NAME,
        DATA_TYPE,
        CASE 
           WHEN DATA_TYPE IN ('tinyint','smallint','int','bigint') THEN NULL
           ELSE NUMERIC_PRECISION
        END AS NUMERIC_PRECISION,
        NUMERIC_SCALE,
        CASE 
           WHEN DATA_TYPE IN ('smalldatetime','datetime','date') THEN NULL
           ELSE DATETIME_PRECISION
        END AS DATETIME_PRECISION,
        CHARACTER_MAXIMUM_LENGTH

     FROM 
        INFORMATION_SCHEMA.DOMAINS
  ) T

（仅针对我自己使用的类型进行测试）

- limit

Answer 3

要获取用户定义类型的底层系统类型名称以及其属性，请执行以下操作：

USE AdventureWorks
SELECT 
    u.name AS userdefined,
    s.name AS underlying,
    u.precision,
    u.scale,
    u.max_length
FROM sys.types u
INNER JOIN sys.types s
    ON u.system_type_id = s.user_type_id
WHERE u.is_user_defined = 1
    AND u.name = 'Name'

结果：

userdefined underlying  precision   scale   max_length
Name        nvarchar    0           0       100

所以，AdventureWorks中的用户定义类型'Name'实际上是nvarchar(100)的别名。

[抱怨]

鉴于MS SQL用户定义类型只是底层系统类型的别名，它们只不过是装饰品而已。

例如，据我所知，无法定义一个由2个整数和相关算术操作组成的用户定义类型'Rational'。

[/抱怨]**

- smirkingman