Press "Enter" to skip to content

Defining Table-Valued Function Metadata

Bill Fellows has a query which gets input parameters for table-valued functions:

In my ever expanding quest for getting all the metadata, I how could I determine the metadata for all my table valued functions? No problem, that’s what sys.dm_exec_describe_first_result_set is for. SELECT * FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM', N'@xmlMessage nvarchar(max)', 1) AS DEDFRS

Except, I need to know parameters. And I need to know parameter types. And order. Fortunately, sys.parameters and sys.types makes this easy. The only ugliness comes from the double invocation of row rollups

Click through for the script.