0

How to check whether table has UDT or not and if yes, how to get list of UDT's in a given table in SQL Server

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Rakesh
  • 3
  • 2

1 Answers1

0

This should do it:

SELECT c.[name] AS ColumnName, ct.[name] AS UDTName
FROM sys.tables t 
     JOIN sys.columns c ON t.object_id = c.object_id
     JOIN sys.types ct ON c.user_type_id = ct.user_type_id
WHERE t.[name] = 'YourTableName'
  AND ct.is_user_defined = 1;
Thom A
  • 88,727
  • 11
  • 45
  • 75
  • @marc_s thanks for correcting the typo on `ColumnName`, but please don't change my SQL to your formatting preferences. :) – Thom A Jan 19 '18 at 11:27
  • it works, for full table name we can use " Where c.object_id = OBJECT_ID('[Schema].[TableName]') " – Rakesh Jan 24 '18 at 09:10
  • SELECT * FROM sys.columns c INNER JOIN sys.types t ON c.user_type_id = t.user_type_id WHERE c.object_id = OBJECT_ID('[Schema].[TableName]') AND t.is_user_defined = 1 – Rakesh Jan 24 '18 at 09:20