60

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.

Kermit
  • 33,827
  • 13
  • 85
  • 121
stackuser83
  • 2,012
  • 1
  • 24
  • 41
  • 1
    possible duplicate of [How do I list user defined types in a SQL Server database?](http://stackoverflow.com/questions/54482/how-do-i-list-user-defined-types-in-a-sql-server-database) – Kermit Mar 14 '13 at 21:09
  • 5
    @AarolamaBluenk this is not a duplicate of that question at all. this is looking for udf's and their definitions, not udt's... – rsbarro Mar 14 '13 at 21:15

6 Answers6

95
SELECT name, definition, type_desc 
  FROM sys.sql_modules m 
INNER JOIN sys.objects o 
        ON m.object_id=o.object_id
WHERE type_desc like '%function%'
Community
  • 1
  • 1
RandomUs1r
  • 4,010
  • 1
  • 24
  • 44
  • 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. – stackuser83 Mar 15 '13 at 13:45
8

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
Geoff
  • 8,551
  • 1
  • 43
  • 50
8
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')
M--
  • 25,431
  • 8
  • 61
  • 93
Srikanth CHindam
  • 91
  • 1
  • 5
  • 1
    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). – Srikanth CHindam Jun 07 '17 at 09:26
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_')
JeffJak
  • 2,008
  • 5
  • 28
  • 40
0

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
Matthew Sidorick
  • 41
  • 3
0

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
rforceteam
  • 49
  • 6