SQL list of all the user defined functions in a database

Question

I am looking for a SQL query that outputs the function definitions for all of the user defined functions in a database catalog.

I have found as far as

SELECT OBJECT_DEFINITION (OBJECT_ID(N'dbo.UserFunctionName')) AS [Object Definition]

and

SELECT ROUTINE_NAME FROM information_schema.routines WHERE routine_type = 'function'

but I can't think of or find a way to feed the ROUTINE_NAME list to the OBJECT_ID.

The purpose here is a searchable text of the user defined function definitions in a database for database change analysis, if something like a full SQL procedure or purposed helper program is easier, I will do that and post it.

Corettacorette

possible duplicate of How do I list user defined types in a SQL Server database? – Bores

@AarolamaBluenk this is not a duplicate of that question at all. this is looking for udf's and their definitions, not udt's... – Hoopen

Answer 1

SELECT o.name, m.definition, o.type_desc 
  FROM sys.sql_modules m 
INNER JOIN sys.objects o 
        ON m.object_id=o.object_id
WHERE o.type_desc like '%function%'

Bioscopy

This answer is better for allowing easier inclusion of other columns of info if needed. Geoff's CTE also fits the bill great, I think the CTEs are a useful technique I wish I had known for other situations. Knowing about sys.sql_modules and its object_id key reference, from RandomUs1r, an IN(SELECT SUBQUERY) was best for my purposes. – Corettacorette

Answer 2

You could use a CTE:

with functions(routine_name) as 
  (SELECT ROUTINE_NAME FROM information_schema.routines WHERE routine_type = 'function')
select 
  OBJECT_DEFINITION(OBJECT_ID(routine_name)) AS [Object Definition] 
from 
  functions

Germano

Answer 3

SELECT O.name, M.definition, O.type_desc, O.type
FROM sys.sql_modules M
INNER JOIN sys.objects O ON M.object_id=O.object_id
WHERE O.type IN ('IF','TF','FN')

Distinctly

Hi tinamou, Here, we are fetching function's name, definition and Type of user defined function. user defined function can be Inline function(IF) or Scalar function(FN) or table valued function(TF). – Distinctly

Answer 4

Similar to this solution: Query to list all stored procedures

SELECT * 
  FROM DIDS0100.INFORMATION_SCHEMA.ROUTINES
 WHERE ROUTINE_TYPE = 'FUNCTION' 
    AND LEFT(ROUTINE_NAME, 3) NOT IN ('fn_')

Haler

Answer 5

Here is a version that includes schema, and is formatted to allow the mass-dropping of un-needed scalar-valued functions:

SELECT ('DROP FUNCTION [' + SCHEMA_NAME(o.schema_id) + '].[' + o.name + ']') AS ObjectName 
FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_id = o.object_id 
WHERE o.type_desc = 'SQL_SCALAR_FUNCTION' 
ORDER BY ObjectName

Perales

Answer 6

Generate a parameter list for all SQL Server stored procedures and functions

SELECT 
   SCHEMA_NAME(SCHEMA_ID) AS [Schema]
  ,SO.name AS [ObjectName]             
  ,SO.Type_Desc AS [ObjectType (UDF/SP)]
  ,P.parameter_id AS [ParameterID]
  ,P.name AS [ParameterName]
  ,TYPE_NAME(P.user_type_id) AS [ParameterDataType]
  ,P.max_length AS [ParameterMaxBytes]
  ,P.is_output AS [IsOutPutParameter]
FROM sys.objects AS SO
INNER JOIN sys.parameters AS P ON SO.OBJECT_ID = P.OBJECT_ID
ORDER BY [Schema], SO.name, P.parameter_id

Autonomic