get column list of a TVF - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

get column list of a TVF

Author

Topic

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2010-05-13 : 03:16:41

anyone know how to get the list of columns returned by a table valued function, without calling it? I was thinking they would be in sys.columns or INFORMATION_SCHEMA.COLUMNS but they are not.

all I can come up with is this (assuming the function takes a single param here)

set fmtonly on
select top 1 * from dbo.my_tvf(null)
set fmtonly off

but I'd rather get the data from the system catalog if it's possible.

also would like to avoid parsing the DDL to discover the cols returned. :)

elsasoft.org

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-05-13 : 04:22:45

EXEC sp_help can help here! Example


IF OBJECT_ID('CG_FUNC_xyz') IS NOT NULL DROP FUNCTION CG_FUNC_xyz
GO

CREATE FUNCTION CG_FUNC_xyz ()
RETURNS TABLE AS RETURN
SELECT
	1 AS [1], 2 AS [2nd Column]
UNION SELECT
	2 AS [1], 6 AS [2nd Column]
GO

EXEC sp_help 'CG_FUNC_xyz'

The 2nd result set returns:


Column_name	Type	Computed	Length	Prec	Scale	Nullable	TrimTrailingBlanks	FixedLenNullInSource	Collation
1	int	no	4	10   	0    	no	(n/a)	(n/a)	NULL
2nd Column	int	no	4	10   	0    	no	(n/a)	(n/a)	NULL

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-05-13 : 04:28:08

It also works for multi statement tvf


IF OBJECT_ID('CG_FUNC_xyz') IS NOT NULL DROP FUNCTION CG_FUNC_xyz
GO

CREATE FUNCTION CG_FUNC_xyz ()
RETURNS @foo TABLE (
		[a] BIT
		, [b] BIT
	)
	AS
BEGIN
	INSERT @foo SELECT 1, 0
	RETURN 
END
GO

EXEC sp_help 'CG_FUNC_xyz'

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-13 : 04:40:26

select * from INFORMATION_SCHEMA.PARAMETERS
where specific_name='my_tvf'

Madhivanan

Failing to plan is Planning to fail

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-05-13 : 04:45:46

quote:
Originally posted by madhivanan

select * from INFORMATION_SCHEMA.PARAMETERS
where specific_name='my_tvf'

LOL -- yeah, or you could do that!

oops. Don't know why I didn't remember that.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2010-05-13 : 09:57:20

quote:
Originally posted by Transact Charlie

quote:
Originally posted by madhivanan

select * from INFORMATION_SCHEMA.PARAMETERS
where specific_name='my_tvf'

LOL -- yeah, or you could do that!

oops. Don't know why I didn't remember that.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

It's good that you didn't remember that Charlie! because that tells you the params, not the columns returned.

sp_help works though - thanks!

elsasoft.org

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-13 : 10:06:03

This will return the columns returned

select * from INFORMATION_SCHEMA.ROUTINE_COLUMNS
where table_name='my_tvf'

Madhivanan

Failing to plan is Planning to fail

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2010-05-13 : 10:10:06

cool, thanks!

elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources