Transact SQL – User Defined Table Types – Metadata


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.



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


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



Here is a sample code that can help…


    1) Get name and definition of all table types

            = 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 (
                            in ( 
                                    , 'nvarchar'
                                    , 'ntext'
                        then tblSC.max_length / 2

        , [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


    , tblSTT.[name]

    , tblSC.[column_id]


Referenced Work

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


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

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s