如何检查 SQL Server 中存储过程或函数的上次更改日期

Question

我需要检查上次功能更改的时间。我知道如何检查创建日期（它位于 SQL Server Management Studio 的函数属性窗口中）。
我发现在 SQL Server 2000 中无法检查修改日期（看看这篇文章：是否可以确定 SQL Server 2000 中存储过程的最后修改时间？）

是否可以在 SQL 中检查它服务器2008？ MS 是否在系统表中添加了一些允许检查它的新功能？

Answer 1

SELECT name, create_date, modify_date 
FROM sys.objects
WHERE type = 'P'
ORDER BY modify_date DESC

函数的类型是FN，而不是过程的P。或者您可以过滤名称列。

Answer 2

对于存储过程试试这个：

SELECT name, create_date, modify_date
FROM sys.objects
WHERE type = 'P'
AND name = 'myProc'

Answer 3

这是查找函数的正确解决方案：

SELECT name, create_date, modify_date
FROM sys.objects
WHERE type = 'fn'
AND name = 'fn_NAME'

Answer 4

我发现这被列为新技术

这非常详细

SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = N'PROCEDURE' and ROUTINE_SCHEMA = N'dbo' 
order by  LAST_ALTERED desc

SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = N'PROCEDURE' and ROUTINE_SCHEMA = N'dbo' 
order by  CREATED desc 


SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = N'FUNCTION' and ROUTINE_SCHEMA = N'dbo' 
order by  LAST_ALTERED desc

SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = N'FUNCTION' and ROUTINE_SCHEMA = N'dbo' 
order by  CREATED desc 

Answer 5

在最新版本（2012或更高版本）中，我们可以使用此查询获取修改后的存储过程详细信息

SELECT create_date, modify_date, name FROM sys.procedures 
ORDER BY modify_date DESC

Answer 6

对于 SQL 2000 我会使用：

SELECT name, crdate, refdate 
FROM sysobjects
WHERE type = 'P' 
ORDER BY refdate desc

Answer 7

您可以使用它来检查按日期排序的函数和存储过程的修改日期：

SELECT 'Stored procedure' as [Type] ,name, create_date, modify_date 
FROM sys.objects
WHERE type = 'P' 

UNION all

Select 'Function' as [Type],name, create_date, modify_date
FROM sys.objects
WHERE type = 'FN'
ORDER BY modify_date DESC

或：

SELECT type ,name, create_date, modify_date 
FROM sys.objects
WHERE type in('P','FN') 
ORDER BY modify_date DESC
-- this one shows type like : FN for function and P for stored procedure

结果将如下所示：

Type                 |  name      | create_date              |  modify_date
'Stored procedure'   | 'firstSp'  | 2018-08-04 07:36:40.890  |  2019-09-05 05:18:53.157
'Stored procedure'   | 'secondSp' | 2017-10-15 19:39:27.950  |  2019-09-05 05:15:14.963
'Function'           | 'firstFn'  | 2019-09-05 05:08:53.707  |  2019-09-05 05:08:53.707

Answer 8

SELECT *
FROM sys.objects
WHERE type IN ('FN', 'IF', 'TF') 
AND name = 'dgdsgds'

Answer 9

SELECT name, created_at, updated_at 
FROM table_name.column_name
WHERE type = 'soemthing'
ORDER BY updated_at DESC ;

我希望这会帮助你