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'
精彩评论