SQL SERVER – Find All The User Defined Functions (UDF) – Part 2

Few days ago, I wrote about SQL SERVER – Find All The User Defined Functions (UDF) in a Database. Regular reader of this blog Madhivanan has suggested following alternate method to do the same task of finding all the user defined functions in database.

USE AdventureWorks
GO
SELECT specific_name,specific_schema
FROM information_schema.routines
WHERE routine_type='function'
GO

Another Way to Find All the User Defined Functions

The INFORMATION_SCHEMA views are handy because they follow the SQL standard, so the same idea works on other database platforms too. When I work only on SQL Server, I like the catalog view sys.objects better, because it shows the exact kind of each function in a simple type code. Here is the query I use today to find all the user defined functions in a database, along with the schema and the type of each one.

SELECT SCHEMA_NAME(o.schema_id) AS SchemaName,
       o.name AS FunctionName,
       o.type_desc
FROM sys.objects AS o
WHERE o.type IN ('FN', 'IF', 'TF', 'FS', 'FT')
ORDER BY SchemaName, FunctionName;

The type codes are easy to read. FN is a scalar function, IF is an inline table-valued function and TF is a multi-statement table-valued function. FS and FT are the CLR versions of scalar and table-valued functions. One more small tip: if you want to read the code of a function, do not depend on the ROUTINE_DEFINITION column of INFORMATION_SCHEMA.ROUTINES, because it returns only the first 4000 characters. Use OBJECT_DEFINITION or sys.sql_modules instead and you will get the full text, no matter how long the function is.

I keep this query in my snippets folder and run it every time I join a new project, because it gives me a quick picture of how much logic lives inside functions. If the list is long, it is also a good hint to look at the scalar functions for performance. A scalar function called for every row of a big query can slow that query down a lot, and this list tells you where to start looking.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.