How can I get user defined types definition in SQL Server 2008 R2 using SQL query?

Question

I have created a sample java program in which I get the definition of all objects like views, triggers, functions, etc. using the following queries:

select object_definition(object_id) 
from sys.objects 
where type = 'V'; //for Views

select object_definition(object_id) 
from sys.objects 
where type = 'TF'; //for Functions

select object_definition(object_id) 
from sys.objects 
where type = 'TR'; //for triggers

But sys.objects doesn't contain the user-defined data types and user-defined table types. I am using the following query to get user-defined types:-

select * 
from sys.types 
where is_user_defined = 1;

Is there any sql query using which I can get the definition for user-defined data types and user-defined table types?

strickt01 strickt01 · Accepted Answer · 2016-02-29T13:14:00

The user-defined type in itself won't have an object definition - it is a type not an object.

For the user defined table types you could get information from sys.table_types but you won't get an object_definition from the type_table_object_id of this anymore than you would from the object_id of a user table in sys.objects. You might want to look at this link for creating a table creation script:

Generate Create Table Script

For your UDTs you'd have to follow a similar path and write something that queried the system tables for information on the type and then built up a SQL string accordingly. You'd need to run SQL like (based on trace of server when scripting a type creation):

exec sp_executesql N'SELECT
st.name AS [Name],
sst.name AS [Schema],
ISNULL(s1st.name, N'''') AS [Owner],
CAST(case when st.principal_id is null then 1 else 0 end AS bit) AS     [IsSchemaOwned],
st.user_type_id AS [ID],
CAST(CASE WHEN baset.name IN (N''nchar'', N''nvarchar'') AND st.max_length <> -1 THEN st.max_length/2 ELSE st.max_length END AS int) AS [Length],
CAST(st.precision AS int) AS [NumericPrecision],
CAST(st.scale AS int) AS [NumericScale],
CAST(CASE WHEN baset.name IN (N''decimal'', N''int'', N''numeric'', N''smallint'', N''tinyint'', N''bigint'') THEN 1 ELSE 0 END AS bit) AS [AllowIdentity],
st.max_length AS [MaxLength],
st.is_nullable AS [Nullable],
(case when st.default_object_id = 0 then N'''' else def.name end) AS [Default],
(case when st.default_object_id = 0 then N'''' else schema_name(def.schema_id) end) AS [DefaultSchema],
(case when st.rule_object_id = 0 then N'''' else rul.name end) AS [Rule],
(case when st.rule_object_id = 0 then N'''' else schema_name(rul.schema_id) end) AS [RuleSchema],
ISNULL(st.collation_name, N'''') AS [Collation],
CAST(CASE WHEN baset.name IN ( N''varchar'', N''varbinary'', N''nvarchar'' )     THEN 1 ELSE 0 END AS bit) AS [VariableLength],
baset.name AS [SystemType]
FROM
sys.types AS st
INNER JOIN sys.schemas AS sst ON sst.schema_id = st.schema_id
LEFT OUTER JOIN sys.database_principals AS s1st ON s1st.principal_id =     ISNULL(st.principal_id, (TYPEPROPERTY(QUOTENAME(SCHEMA_NAME(st.schema_id)) + ''.'' + QUOTENAME(st.name), ''OwnerId'')))
LEFT OUTER JOIN sys.types AS baset ON (baset.user_type_id = st.system_type_id and baset.user_type_id = baset.system_type_id) or ((baset.system_type_id = st.system_type_id) and (baset.user_type_id = st.user_type_id) and (baset.is_user_defined = 0) and (baset.is_assembly_type = 1)) 
LEFT OUTER JOIN sys.objects AS def ON def.object_id = st.default_object_id
LEFT OUTER JOIN sys.objects AS rul ON rul.object_id = st.rule_object_id
WHERE
(st.schema_id!=4 and st.system_type_id!=240 and st.user_type_id != st.system_type_id and st.is_table_type != 1)and(st.name=@_msparam_0 and sst.name=@_msparam_1)',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000)',@_msparam_0=N't_your_udt',@_msparam_1=N'your_schema_name'

How can I get user defined types definition in SQL Server 2008 R2 using SQL query?

1 Answers