从Sql Server查询所有用户定义的类型？

How can I query all user defined data types from sql server like the way I do this for database object which are stored in sysobjects.

如何从sql server查询所有用户定义的数据类型,就像我对存储在sysobjects中的数据库对象一样。

6 个解决方案

#1

In SQL Server 2005 (and above) you can do this

在SQL Server 2005(及更高版本)中,您可以执行此操作

SELECT * FROM sys.Types WHERE is_user_defined = 1

If you are on SQL Server 2000, you can use SysTypes. I think the query is as follows:

如果您使用的是SQL Server 2000,则可以使用SysTypes。我认为查询如下:

SELECT * FROM SysTypes WHERE xusertype > 256

#2

If you are still on SQL Server 2000 (as your use of 'sysobjects' suggests), look in systypes. If you are on SQL Server 2005 or later, as rwwilden says you should use sys.types (and move to using sys.objects rather than sysobjects!)

如果您仍然使用SQL Server 2000(正如您使用'sysobjects'建议的那样),请查看systypes。如果你在SQL Server 2005或更高版本上,正如rwwilden所说,你应该使用sys.types(并转而使用sys.objects而不是sysobjects!)

#3

You can use the sys.types view for this. When user_type_id and system_type_id are different, you have a user type.

您可以使用sys.types视图。当user_type_id和system_type_id不同时,您有一个用户类型。

As AakashM says, the sys.types view is only available from SQL Server 2005 and higher.

正如AakashM所说,sys.types视图仅在SQL Server 2005及更高版本中可用。

EDIT: A better way to determine whether a type is a user-defined type is to check the is_user_defined flag (see Tim C's answer).

编辑:确定类型是否是用户定义类型的更好方法是检查is_user_defined标志(参见Tim C的答案)。

#4

There is no standard query to get all the User defined databases alone. We can use

没有标准查询可以单独获取所有用户定义的数据库。我们可以用

select * from sys.databases

But it shows all the Databases, I'm using Server management studio. So by default i have the following databases named : 'Master','tempdb','msdb','model'

但它显示了所有的数据库,我正在使用服务器管理工作室。所以默认情况下,我有以下数据库:'Master','tempdb','msdb','model'

So i always use this following query to get the user defined databases.

所以我总是使用以下查询来获取用户定义的数据库。

select * from sys.databases where name not in('master','model','msdb','tempdb')

It works for fine. If you have extra databases by default, it will be the Following (resource,distribution,reportservice, reportservicetemp). Just use the names in that query.

它很好用。如果默认情况下有额外的数据库,则它将是以下(资源,分发,reportservice,reportservicetemp)。只需使用该查询中的名称即可。

#5

You can use the INFORMATION_SCHEMA views. INFORMATION_SCHEMA.ROUTINES has UDFs and Stored Procs. INFORMATION_SCHEMA.TABLES has tables and views.

您可以使用INFORMATION_SCHEMA视图。 INFORMATION_SCHEMA.ROUTINES具有UDF和存储过程。 INFORMATION_SCHEMA.TABLES有表格和视图。

#6

Actually system_type_id being different from user_type_id is NOT a good indication. Note that this:

实际上,system_type_id与user_type_id不同并不是一个好的指示。请注意:

SELECT * FROM sys.Types 
WHERE (system_type_id <> user_type_id)
AND is_user_defined = 0.

Returns these:

hierarchyid
geometry
geography
sysname

So use SELECT * FROM sys.types WHERE is_user_defined = 1 as was pointed out by Tim C and Ronald Wildenberg

所以使用SELECT * FROM sys.types WHERE is_user_defined = 1正如Tim C和Ronald Wildenberg所指出的那样

#1