开发者

How to select all the columns for a user-defined type

I have created some user-defined types to be used as table-valued parameters. Is there any way I could select their columns, just like I can select columns for a table:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE 'MyTable'

Edit: yes, I tried to read MSDN, but I don't see the information there. My current workaround is to create a permanent table as follows:

CREATE TABLE Placeholder开发者_运维知识库(copy-and-paste all the columns from my type here)

Then I can select from INFORMATION_SCHEMA.COLUMNS and drop Placeholder when I am done.


This will give you a list of columns for the table type TVPTest

select c.*
from sys.table_types as tt
  inner join sys.columns as c
    on tt.type_table_object_id = c.object_id
where tt.name = 'TVPTest'


The accepted answer works well, but I extended it a bit to get more details:

select 
    c.name
    ,ColumnType = t.name
    ,ColumnLength = c.max_length
from sys.table_types as tt
  join sys.columns as c
    on tt.type_table_object_id = c.object_id    
  join sys.types t
  on t.system_type_id = c.system_type_id
  and t.user_type_id = c.user_type_id
  --  and tt.type_table_object_id = c.object_id
where tt.name = 'StatusDetail'
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜