CODEWITHSUNDEEP
sqlsql-server
Ramesh Soni
Ramesh Soni

Reputation: 16077

Query all user defined types from 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.

Upvotes: 3

Views: 10887

Answers (7)

Guido Smeets
Guido Smeets

Reputation: 64

You can also find these in INFORMATION_SCHEMA.DOMAINS. personally I prefer to use that over the sys schema, as it's an ANSI standard that works over multiple types of databases.

Upvotes: 0

Balaji
Balaji

Reputation: 1515

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.

Upvotes: 2

Charles Kincaid
Charles Kincaid

Reputation: 41

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

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

Upvotes: 0

mattmc3
mattmc3

Reputation: 18335

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

Upvotes: 0

Ronald Wildenberg
Ronald Wildenberg

Reputation: 32094

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

Upvotes: 1

Tim C
Tim C

Reputation: 70618

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

Upvotes: 7

AakashM
AakashM

Reputation: 63338

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

Upvotes: 1

Related Questions