SQL list of all the user defined functions in a database

Sql ServerSql Server-2008-R2

Sql Server Problem Overview

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.

Sql Server Solutions

Solution 1 - Sql Server

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%'

Solution 2 - Sql Server

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

Solution 3 - Sql Server

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')

Solution 4 - Sql Server

Similar to this solution: https://stackoverflow.com/questions/219434/query-to-list-all-stored-procedures

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

Solution 5 - Sql Server

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
Categories
Recommended Sql Server Solutions
Recommended Sql Server-2008-R2 Solutions

Previous Solution:

How to debug stored procedures with print statements?

Sql ServerTsqlStored Procedures

Next Solution:

How to force inline divs to stay on same line?

HtmlCssWord Wrap

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
Questionstackuser83View Question on Stackoverflow
Solution 1 - Sql ServerRandomUs1rView Answer on Stackoverflow
Solution 2 - Sql ServerGeoffView Answer on Stackoverflow
Solution 3 - Sql ServerSrikanth CHindamView Answer on Stackoverflow
Solution 4 - Sql ServerJeffJakView Answer on Stackoverflow
Solution 5 - Sql ServerMatthew SidorickView Answer on Stackoverflow