Background

Thankfully Microsoft SQL Server has a wealth of tools that one can avail towards data encapsulation.

 

User-Defined Table Types

User-Defined table types can be used to encapsulate data and inch towards object-oriented packaging.

The same goes for other table declarations such as table variables, temporary tables ( session temporary tables and global temporary tables ).

 

Short Comings

I think user-defined table types are not used more because of certain shortcomings.

 

Metadata

In this post, I will succinctly touch on one of those shortcomings.

How can one review the structure of a user-defined table type?

 

Stored Procedures

sp_help

Unfortunately, sp_help does not inform us of a user-defined table type’s structure.

 

Code

Here is a sample code that can help…

/*

    1) Get name and definition of all table types
    https://dba.stackexchange.com/questions/161368/get-name-and-definition-of-all-table-types

*/
select 
          [schema]
            = tblSS.[name]

        , [type] 
            = tblSTT.[name]

        , [isUserDefined]
            = tblSTT.[is_user_defined]

        , [isAssemblyType]
            = tblSTT.[is_assembly_type]

        /*
                , [objectid]
                    = tblSTT.[type_table_object_id]
        */

        , [columnID]
            = tblSC.[column_id]

        , [column]
            = tblSC.[name]

        , [colummnType] 
            = tblSTC.[name]

        , [maxLength]
            = tblSC.max_length

        , [maxLengthNormalized]
            = case

                when ( tblSC.max_length = -1)
                    then 'max'

                when (
                        tblSTC.[name]
                            in ( 
                                      'nchar'
                                    , 'nvarchar'
                                    , 'ntext'
                               )
                    )
                        then tblSC.max_length / 2
                else    
                    tblSC.max_length
              end

        , [collation]
            = tblSC.collation_name

        , isNullable
            = tblSC.is_nullable

from   sys.table_types tblSTT

inner join sys.schemas tblSS

        on tblSTT.[schema_id] = tblSS.[schema_id]

inner join sys.columns tblSC

        on tblSTT.type_table_object_id = tblSC.[object_id]

INNER JOIN sys.types AS tblSTC

        ON tblSC.user_type_id = tblSTC.user_type_id
        AND tblSC.system_type_id = tblSTC.system_type_id

order by

      tblSS.[name]

    , tblSTT.[name]

    , tblSC.[column_id]

Output

Referenced Work

1. Stack Exchange
   • Get Name and Definition of all table types
     Link

Dedicated

Always in dedication to Stack Overflow and the millions of every day and not so everyday contributors.