为用户定义的类型查找SQL基类型

I am working with a legacy database that has a large number of user-defined SQL types. I am writing a method in .NET in which I am defining parameters in the SqlParameter object. I need the underlying SQL types for the user defined types in order to properly define the parameters as I create them dynamically at runtime.

我正在处理一个具有大量用户定义SQL类型的遗留数据库。我在。net中编写一个方法，在其中我在SqlParameter对象中定义参数。我需要用户定义类型的底层SQL类型，以便在运行时动态创建参数时正确定义参数。

To do this I created this procedure:

为此，我创建了以下程序:

(@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'

I am wondering if this is the best way to go about getting the underlying base type for a user defined type?

我想知道这是否是获取用户定义类型的底层基类型的最好方法?

2 个解决方案

#1

You shouldn't be using systypes or syscolumns - these are backward compatibility views, and sys.types and sys.columns are highly preferred unless you are trying to write code that works on SQL Server 2000+ (which I don't recommend either).

您不应该使用system - pes或syscolumns—这些是向后兼容视图和sys。类型和系统。除非您正在尝试编写在SQL Server 2000+上工作的代码(我也不推荐)，否则列是非常受欢迎的。

To get the information about a type you already know the name of:

要获取有关类型的信息，您已经知道类型的名称:

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

To get the information for all the user-defined types in a database:

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

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

To get the information about all the types (system and user-defined) for a specific table:

获取关于特定表的所有类型(系统和用户定义)的信息:

UPDATE to include the base type:

更新以包括基本类型:

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');

Note that this will return two rows if you use alias types (e.g. CREATE TYPE blat FROM nvarchar(32);). If you really must use those (I recommend against them, also), then change the join clause to:

注意，如果使用别名类型(例如，从nvarchar(32)创建类型blat)，这将返回两行。如果您确实必须使用它们(我也建议不要使用它们)，那么将join子句更改为:

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

#2

To get the information for all the user-defined types in a database you can use INFORMATION_SCHEMA.DOMAINS:

要获取数据库中所有用户定义类型的信息，可以使用information_schema . domain:

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

FROM 
   INFORMATION_SCHEMA.DOMAINS

UPD: The following query return human readable definition for all user defind types:

UPD:以下查询返回人类可读定义的所有用户defind类型:

  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

(tested only for types that i am using myself)

(只测试我正在使用的类型)

#1