Count SQL Server User Created Functions based on Type

Question

I can count total user created functions in SQL Server using

SELECT COUNT(*)
FROM information_schema.routines
WHERE routine_type = 'FUNCTION'

But this returns all functions whether it be a scalar-valued function, an inline function or a table-valued function

Is there a way to obtain a count specific to the type of function? E.g. count inline functions only?

Helpful, I can get the total inline functions and table valued functions then subtract that from the total funtions to get the scalar ones. But is there a way to seperate the table valued and inline ones? — Tyro
– Tyro, Commented Jul 2, 2020 at 3:31

Roger Wolf · Accepted Answer · 2020-07-02 03:35:50Z

2

This distinction you are after is specific to SQL Server and probably not covered by the information_schema standard. You need to look at system views for that:

select o.type_desc, count(*)
from sys.objects o
where o.type in ('AF', 'FN', 'FS', 'FT', 'IF', 'TF')
group by o.type_desc
order by o.type_desc;

Depending on the version of SQL Server you are using, the list of available object types might differ. Consult with the documentation for your version regarding that.

answered Jul 2, 2020 at 3:35

Roger Wolf

7,7972 gold badges29 silver badges37 bronze badges

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

Collectives™ on Stack Overflow

Count SQL Server User Created Functions based on Type

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related