Menu
Is any way to get return table schema from table valued functions ?
Now i have only one idea, execute
SELECT ROUTINE_SCHEMA,
ROUTINE_NAME,
ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
and parse the function query, but it isn't gread idea. :/
293
asked Jan 16 '12 16:01
The simple definition of the table-valued function (TVF) can be made such like that; a user-defined function that returns a table data type and also it can accept parameters. TVFs can be used after the FROM clause in the SELECT statements so that we can use them just like a table in the queries.
Use sys. For example, if we want to search only for the user-defined table, we use 'U' value for the type column. I filter records for the useful columns. You can get all columns using the select * statement. Similarly, we use the value 'P' for the stored procedure.
A table-valued function returns a single rowset (unlike stored procedures, which can return multiple result shapes). Because the return type of a table-valued function is Table , you can use a table-valued function anywhere in SQL that you can use a table.
You can get the information from INFORMATION_SCHEMA.ROUTINE_COLUMNS
e.g.
SELECT *
FROM INFORMATION_SCHEMA.ROUTINE_COLUMNS
WHERE TABLE_NAME = 'YourTableValuedFunctionName'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
