Get name and definition of all table types

Question

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

Answer 1

You can see the list of names:

SELECT name FROM sys.table_types;

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

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):

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

Answer 2

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