Query to List Table-Valued Functions and their Input Arguments?

Question

The following query will return a list of all table-valued functions in a database and a list of their output columns (and column data types).

USE DATABASENAME
GO

SELECT
rc.*
FROM
INFORMATION_SCHEMA.ROUTINES r INNER JOIN
INFORMATION_SCHEMA.ROUTINE_COLUMNS rc ON
r.ROUTINE_CATALOG = rc.TABLE_CATALOG AND
r.ROUTINE_SCHEMA = rc.TABLE_SCHEMA AND
r.ROUTINE_NAME = rc.TABLE_NAME
WHERE
r.ROUTINE_TYPE = 'FUNCTION' AND
r.DATA_TYPE = 'TABLE'

But is there a way to query for a list all table-valued functions in a database along with a list of their input arguments (and argument data types)?

I looked here but didn't find a solution.

I noticed that there is a column named ROUTINE_DEFINITION in the INFORMATION_SCHEMA.ROUTINES table which shows the full script that created the table-valued function, so I suppose it is possible to parse through this string and find the input data types that you're looking for, although this would be very complicated especially considering the programming comments are included in this ROUTINE_DEFINITION which only further complicates things.

or from INFORMATION_SCHEMA.PARAMETERS if you prefer from the same schema — Squirrel
– Squirrel, Commented Apr 17, 2021 at 12:35
@Squirrel Awesome! Did you want to post an answer? I can mark it as correct. — ImaginaryHuman072889
– ImaginaryHuman072889, Commented Apr 17, 2021 at 13:37

Charlieface · Accepted Answer · 2024-03-28 11:35:45Z

5

INFORMATION_SCHEMA system tables should be avoided, as they are generalized views for non-specific-to-SQL-Server clients. Instead use the sys schema's tables and views.

Unfortunately there is no sys.functions so we have to filter sys.objects instead:

select o.name, p.*, t.name as type_name
from sys.parameters p
join sys.types t on t.user_type_id = p.user_type_id
join sys.objects o on o.object_id = p.object_id
where o.[type] in ('IF', 'TF') -- IF is inline, TF is multi-statement

edited Mar 28, 2024 at 11:35

answered Apr 17, 2021 at 22:58

Charlieface

78.7k8 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Austin_G Over a year ago

Make sure to use left joins if you want to include table value functions that do not have parameters

Collectives™ on Stack Overflow

Query to List Table-Valued Functions and their Input Arguments?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related