如何在 SQL Server 数据库中列出用户定义的类型？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/54482/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I list user defined types in a SQL Server database?
提问by jammycakes
I need to enumerate all the user defined types created in a SQL Serverdatabase with CREATE TYPE, and/or find out whether they have already been defined.
我需要枚举在SQL Server数据库中创建的所有用户定义类型CREATE TYPE，和/或找出它们是否已经被定义。
With tables or stored procedures I'd do something like this:
对于表或存储过程，我会做这样的事情：
if exists (select * from dbo.sysobjects where name='foobar' and xtype='U')
drop table foobar
However I can't find the equivalent (or a suitable alternative) for user defined types! I definitely can't see them anywhere in sysobjects.
但是，我找不到用户定义类型的等效项（或合适的替代方案）！我绝对不能在任何地方看到它们sysobjects。
Can anyone enlighten me?
任何人都可以启发我吗？
回答by jwolly2
Types and UDTs don't appear in sys.objects. You should be able to get what you're looking for with the following:
类型和 UDT 不会出现在 sys.objects 中。您应该能够通过以下方式获得您正在寻找的内容：
select * from sys.types
where is_user_defined = 1
回答by Ron Sanderson
Although the post is old, I found it useful to use a query similar to this. You may not find some of the formatting useful, but I wanted the fully qualified type name and I wanted to see the columns listed in order. You can just remove all of the SUBSTRING stuff to just get the column name by itself.
虽然帖子很旧，但我发现使用类似的查询很有用。您可能不会发现某些格式很有用，但我想要完全限定的类型名称，并且想要查看按顺序列出的列。您可以删除所有 SUBSTRING 内容以单独获取列名。
SELECT USER_NAME(TYPE.schema_id) + '.' + TYPE.name AS "Type Name",
COL.column_id,
SUBSTRING(CAST(COL.column_id + 100 AS char(3)), 2, 2) + ': ' + COL.name AS "Column",
ST.name AS "Data Type",
CASE COL.Is_Nullable
WHEN 1 THEN ''
ELSE 'NOT NULL'
END AS "Nullable",
COL.max_length AS "Length",
COL.[precision] AS "Precision",
COL.scale AS "Scale",
ST.collation AS "Collation"
FROM sys.table_types TYPE
JOIN sys.columns COL
ON TYPE.type_table_object_id = COL.object_id
JOIN sys.systypes AS ST
ON ST.xtype = COL.system_type_id
where TYPE.is_user_defined = 1
ORDER BY "Type Name",
COL.column_id
回答by Ton Plooij
To expand on jwolly2's answer, here's how you get a list of definitions including the standard data type:
要扩展 jwolly2 的答案，以下是获取包括标准数据类型的定义列表的方法：
-- User Defined Type definitions TP 20180124
select t1.name, t2.name, t1.precision, t1.scale, t1.max_length as bytes, t1.is_nullable
from sys.types t1
join sys.types t2 on t2.system_type_id = t1.system_type_id and t2.is_user_defined = 0
where t1.is_user_defined = 1 and t2.name <> 'sysname'
order by t1.name
