CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008
Arne Lund
Arne Lund

Reputation: 2446

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.

Upvotes: 5

Views: 5074

Answers (2)

Sylvia
Sylvia

Reputation: 2616

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'

Upvotes: 1

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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'

Upvotes: 10

Related Questions