Search code examples
sql-servertable-valued-parametersdescribe

SQL Server: Describe a Table-Valued Parameter

What is the equivalent to EXEC sp_columns <table name>; but for SQL Server Table-Valued parameters? Basically, I'm looking for T-SQL or an in-built stored procedure that returns column information (name, data type) for user-created Table-Valued parameters.

Solution

  • You can access table type schema information in the sys.table_types system view:

    select
    s.name as 'Schema',
    t.name as 'Type',
    c.name as 'Column',
    c.*
from sys.table_types t
inner join sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
inner join sys.columns c
    on c.[object_id] = t.type_table_object_id
where is_user_defined = 1

    The table types are also available in the INFORMATION_SCHEMA.DOMAINS view:

    select *
from INFORMATION_SCHEMA.DOMAINS
where DATA_TYPE = 'table type'