Geek asks and answers

Retrieving table function result columns in SQL Server 2008 R2

For the constant generator, I like to get the metadata of the result columns for all my table functions (what are the names of the columns returned by each table function). How can I get them? Do I have to parse the source code of the function or is there an interface that provides this information?

thanks for the help

Chris

The following query that I use to get TVF:

SELECT udf.name AS Name, SCHEMA_NAME(udf.schema_id) AS [Schema]
FROM master.sys.databases AS dtb, sys.all_objects AS udf
WHERE dtb.name = DB_NAME() 
AND (udf.type IN ('TF', 'FT')) 
AND SCHEMA_NAME(udf.schema_id) <> 'sys'
+5
source share
1 answer

This information is available in sys.columns

Returns a row for each column of an object with columns, such as views or tables. The following is a list of the types of objects that have Columns:

  • Tabular Assembly Functions (FT)

  • SQL Internal Table Functions (IF)

  • ()

  • (S)

  • SQL (TF)

  • (U)

  • (V)

SELECT *
FROM sys.columns
WHERE object_id=object_id('dbo.YourTVF')
+7

More articles:


All Articles