SQL Server - “sys.functions”在哪里？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/468672/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server - where is "sys.functions"?
提问by marc_s
SQL Server 2005 has great sys.XXX views on the system catalog which I use frequently.
SQL Server 2005 在我经常使用的系统目录上有很好的 sys.XXX 视图。
What stumbles me is this: why is there a "sys.procedures" view to see info about your stored procedures, but there is no "sys.functions" view to see the same for your stored functions?
让我感到困惑的是：为什么有一个“sys.procedures”视图来查看有关您的存储过程的信息，但没有“sys.functions”视图来查看您的存储函数的信息？
Doesn't anybody use stored functions? I find them very handy for e.g. computed columns and such!
没有人使用存储功能吗？我发现它们对于例如计算列等非常方便！
Is there a specific reason sys.functions is missing, or is it just something that wasn't considered important enough to put into the sys catalog views?? Is it available in SQL Server 2008??
sys.functions 丢失是否有特定原因，或者它只是被认为不够重要的东西，无法放入 sys 目录视图中？它在 SQL Server 2008 中可用吗？？
Cheers, Marc
干杯，马克
回答by LukeH
I find UDFs are very handy and I use them all the time.
我发现 UDF 非常方便，我一直在使用它们。
I'm not sure what Microsoft's rationale is for not including a sys.functions equivalent in SQL Server 2005 (or SQL Server 2008, as far as I can tell), but it's easy enough to roll your own:
我不确定微软在 SQL Server 2005（或 SQL Server 2008，据我所知）中不包含等效的 sys.functions 的理由是什么，但很容易推出自己的：
CREATE VIEW my_sys_functions_equivalent
AS
SELECT *
FROM sys.objects
WHERE type IN ('FN', 'IF', 'TF') -- scalar, inline table-valued, table-valued
回答by Tim C
Another way to list functions is to make use of INFORMATION_SCHEMA views.
列出函数的另一种方法是使用 INFORMATION_SCHEMA 视图。
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'FUNCTION'
According to the Microsoft web site "Information schema views provide an internal, system table-independent view of the SQL Server metadata. Information schema views enable applications to work correctly although significant changes have been made to the underlying system tables". In other words, the underlying System tables may change as SQL gets upgraded, but the views should still remain the same.
根据 Microsoft 网站，“信息架构视图提供了 SQL Server 元数据的内部、独立于系统表的视图。信息架构视图使应用程序能够正常工作，尽管对基础系统表进行了重大更改”。换句话说，底层系统表可能会随着 SQL 升级而改变，但视图应该仍然保持不变。
回答by Orlando Colamatteo
This is valid in 2008 R2 per what SSMS generates when you script a DROP of a function:
根据 SSMS 在编写函数的 DROP 脚本时生成的内容，这在 2008 R2 中有效：
SELECT *
FROM sys.objects
WHERE type IN (N'FN', N'IF', N'TF', N'FS', N'FT') ;
/*
From http://msdn.microsoft.com/en-us/library/ms177596.aspx:
FN SQL_SCALAR_FUNCTION
FS Assembly (CLR) scalar-function
FT Assembly (CLR) table-valued function
IF SQL_INLINE_TABLE_VALUED_FUNCTION
TF SQL_TABLE_VALUED_FUNCTION
*/
回答by Ayresome
It's very slightly more verbose, but this should do exactly the same thing:
它稍微有点冗长，但这应该做完全相同的事情：
select * from sys.objects where (type='TF' or type='FN')
As far as I can see, it's not in SQL Server 2008 either.
据我所知，它也不在 SQL Server 2008 中。
回答by yoniLavi
This does not add anything new, but I found the following easier to remember:
这不会添加任何新内容，但我发现以下内容更容易记住：
select * from sys.objects where type_desc like '%fun%'
回答by vishal kadam
try this :
尝试这个 ：
SELECT * FROM sys.objects
where type_desc = 'SQL_SCALAR_FUNCTION'
回答by Peter Brand
For a fuller description of scalar functions including owner and return type:
有关标量函数的更完整描述，包括所有者和返回类型：
SELECT f.name, s.name AS owner, t.name as dataType, p.max_length, p.precision, p.scale, m.definition
FROM sys.objects f
JOIN sys.schemas s on s.schema_id = f.schema_id
JOIN sys.parameters p on p.object_id = f.object_id AND p.parameter_id = 0
JOIN sys.types t ON t.system_type_id = p.system_type_id
JOIN sys.sql_modules as m ON m.object_id = f.object_id
WHERE type='FN';
回答by MoonKnight
To extend upon @LukeH's answer, to return the function definitions as well requires a join to the sys.sql_modulestable. So the query for this is:
要扩展@LukeH 的答案，返回函数定义也需要连接到sys.sql_modules表。所以对此的查询是：
SELECT O.name as 'Function name', M.definition as 'Definition', O.object_id
FROM sys.objects as O INNER JOIN sys.sql_modules as M
ON O.object_id = M.object_id
WHERE type IN ('FN', 'IF', 'TF') -- scalar, inline table-valued, table-valued
where the above displays the function name, its definition and the object identifier respectively.
上面分别显示了函数名称、定义和对象标识符。
回答by MoonKnight
incidentally, wouldn't you want to include type = 'FS'?
顺便说一句，您不想包含 type = 'FS' 吗？
name type type_desc
getNewsletterStats FS CLR_SCALAR_FUNCTION
that's what the item in sys.objects corresponds with for my UDF which is derived from an external DLL
这就是 sys.objects 中的项目与我的 UDF 对应的项目，该 UDF 是从外部 DLL 派生的
回答by Goran B.
SQL 2000specific, slight adjustment for the object name:
SQL 2000具体的，对对象名称稍作调整：
SELECT *
FROM sysobjects
WHERE type IN ('FN', 'IF', 'TF')
OR
或者
SELECT *
FROM dbo.sysobjects
WHERE type IN ('FN', 'IF', 'TF')
