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

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 2

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'

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.