Query all user defined types from Sql Server?

Question

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.

Answer 1

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

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

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).

Answer 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!)

Answer 3

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

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:

SELECT * FROM SysTypes WHERE xusertype > 256

Answer 4

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