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.
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).
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
!)
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
You can use the INFORMATION_SCHEMA views. INFORMATION_SCHEMA.ROUTINES has UDFs and Stored Procs. INFORMATION_SCHEMA.TABLES has tables and views.