CODEWITHSUNDEEP
sqlsql-server
Lanaa
Lanaa

Reputation: 159

Get Column information of a user defined Table Type

I need to get all information for user defined table type columns (name ,type , null or not ) so I can generate a backup scrip for all table types I got .

Upvotes: 6

Views: 10141

Answers (2)

Brian Jorden
Brian Jorden

Reputation: 1226

The answer @m-ali gave works except if you have User Defined Data Types (basically aliases to primitive data types for consistent usage). For as little change as possible from that query, the following should work for regular types and user defined data types by just swapping the types join to use a different field.

select t.name [tabletypename]
  ,schema_name(t.schema_id) [schemaname]
  ,c.name [column name]
  ,y.name [data type]
  ,c.max_length
  ,c.precision
  ,c.is_identity
  ,c.is_nullable
from sys.table_types t
inner join sys.columns c on c.object_id = t.type_table_object_id
inner join sys.types y on y.user_type_id = c.user_type_id
where t.is_user_defined = 1
  and t.is_table_type = 1

Upvotes: 4

M.Ali
M.Ali

Reputation: 69524

I have just written and tested this on SQL Server 2014, hopefully should work with all versions. 

Select t.name   [TableTypeName]
      ,SCHEMA_NAME(t.schema_id)  [SchemaName]
      ,c.name   [Column Name]
      ,y.name   [Data Type]
      ,c.max_length
      ,c.precision
      ,c.is_identity
      ,c.is_nullable
From sys.table_types t
Inner join sys.columns c on c.object_id = t.type_table_object_id
Inner join sys.types y ON y.system_type_id = c.system_type_id
WHERE t.is_user_defined = 1
  AND t.is_table_type = 1

Upvotes: 16

Related Questions