CODEWITHSUNDEEP
sqlt-sql
user1091406
user1091406

Reputation: 163

Get information about table valued function

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. :/

Upvotes: 7

Views: 4145

Answers (3)

CD Jorgensen
CD Jorgensen

Reputation: 1391

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

You could alternatively dig the information out of the system tables, but that takes more effort, and you can't count on the system tables not changing in a future release. But you could if you wanted:

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

(this example is for SQL Server 2008)

Upvotes: 1

Sparky
Sparky

Reputation: 15085

Here is an approach using the older system tables... Just FYI, AdaTheDev's approach is much better and not likely to break in 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

Upvotes: 0

AdaTheDev
AdaTheDev

Reputation: 147224

You can get the information from INFORMATION_SCHEMA.ROUTINE_COLUMNS

e.g.

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

Upvotes: 11

Related Questions