Get information about a function associated with a table

Question

Is there any way to get the return table schema from table functions?

Now I have only one idea, do

SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES 

and parse the function request, but that is not the idea of ​​gread.:/

Answer 1

You can get information from INFORMATION_SCHEMA.ROUTINE_COLUMNS

eg.

 SELECT * FROM INFORMATION_SCHEMA.ROUTINE_COLUMNS WHERE TABLE_NAME = 'YourTableValuedFunctionName' 

Answer 2

Here's an approach using older system tables ... Just FYI, the AdaTheDev approach is much better and is unlikely to be broken down into future SQL releases.

 select so.name,sc.name,st.name,sc.length,sc.* from sysobjects so join syscolumns sc on sc.id=so.id join systypes st on st.xtype=sc.xtype where so.xtype='TF' and sc.name not like '@%' order by 1,colid 

Answer 3

information_schema.routines is my preferred approach for getting basic information about the function itself. As AdaTheDev stated, information_schema.routine_columns is the best place to get information about columns.

You can also dig up information from system tables, but it takes a lot of effort, and you cannot count on the fact that system tables will not change in a future version. But you could if you wanted to:

 select * from sys.columns where object_id = object_id(N'fnc_Document_GetInfoByIndex') 

(this example is for SQL Server 2008)