I am looking for a SQL query that outputs the function definitions for all of the user defined functions in a database catalog.
我正在寻找一个SQL查询，它输出数据库目录中所有用户定义函数的函数定义。
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.
但我无法想到或找到将ROUTINE_NAME列表提供给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过程或目标帮助程序更容易，我会这样做并发布它。
3 个解决方案
#1
39
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%'
#2
6
You could use a CTE:
您可以使用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
#3
1
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')
#1
39
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%'
#2
6
You could use a CTE:
您可以使用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
#3
1
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')