我需要枚举在SQL Server
数据库中创建的所有用户定义类型CREATE TYPE
,和/或找出它们是否已经定义.
使用表或存储过程,我会做这样的事情:
if exists (select * from dbo.sysobjects where name='foobar' and xtype='U') drop table foobar
但是我找不到用户定义类型的等价物(或合适的替代品)!我绝对不能在任何地方看到它们sysobjects
.
任何人都可以开导我吗?
类型和UDT不会出现在sys.objects中.您应该能够通过以下方式获得所需内容:
select * from sys.types where is_user_defined = 1
虽然帖子很旧,但我发现使用类似的查询很有用.您可能找不到一些有用的格式,但我想要完全限定的类型名称,我希望看到按顺序列出的列.您可以删除所有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