2

I know how to create a table type (to be used as a table-valued parameter) - but once created, how can I view it?

2 Answers 2

5

You can see the list of names:

SELECT name FROM sys.table_types;

To see the definition, you can go to Object Explorer:

enter image description here

If you want to automate this, at least for the columns / data types (this leaves out indexes, keys, etc.) you can build the following:

DECLARE @sql nvarchar(max) = N'', 
  @stub nvarchar(max) = N'SELECT N''$--obj--$'', 
   name, system_type_name
   FROM sys.dm_exec_describe_first_result_set(''DECLARE 
   @tvp $--obj--$; SELECT * FROM @tvp;'',null,null)
   ORDER BY column_ordinal;';

SELECT @sql += REPLACE(@stub, N'$--obj--$', 
  QUOTENAME(s.name) + N'.' + QUOTENAME(t.name))
FROM sys.table_types AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id];

EXEC sys.sp_executesql @sql;

This produces output like the following (one resultset for each table type):

enter image description here

Of course, it will error if you have named a table type with an apostrophe - don't do that).

3

The documentation for sys.table_types view gives a starting point:

Each table type has a type_table_object_id that is a foreign key into the sys.objects catalog view. You can use this ID column to query various catalog views, in a way that is similar to an object_id column of a regular table, to discover the structure of the table type such as its columns and constraints.

The following query returns all table types columns:

SELECT
    tt.name AS table_type_name,
    c.name AS column_name,
    c.column_id,
    t.name AS type_name,
    c.max_length,
    c.precision,
    c.scale,
    c.collation_name,
    c.is_nullable
FROM 
    sys.columns As c
    JOIN sys.table_types AS tt
        ON c.object_id = tt.type_table_object_id
    JOIN sys.types AS t
        ON t.user_type_id = c.user_type_id
ORDER BY
    tt.name,
    c.column_id

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.