如何在 SQL Server 2008 中找到存储过程的最后修改日期、修改用户? [英] How can I find the last modified date, modified user of an stored procedure in SQL Server 2008?

问题描述

我需要找到修改特定存储过程的人的用户名.

I need to find the user name of the person who modified a particular stored procedure.

如何在 Oracle 中最后一次修改或编译存储过程是什么时候?

让我了解时间.但是我怎么知道修改它的用户呢?

gives me idea about the time. But how do I know the user who modified it?

推荐答案

这可以通过运行以下任何查询来实现.

This can be achieve by running any of the following query.

SELECT 
    [procedure] = QUOTENAME(OBJECT_SCHEMA_NAME([object_id]))
        + '.' + QUOTENAME(OBJECT_NAME([object_id])),
    last_execution_time,
    avg_execution_time = CONVERT(DECIMAL(30,2), total_worker_time * 1.0 / execution_count),
    max_worker_time
FROM sys.dm_exec_procedure_stats
WHERE database_id = DB_ID()
ORDER BY avg_execution_time DESC;

------------或------------------------

------------OR--------------------------------------

SELECT 

COALESCE(DB_NAME(t.[dbid]),'Unknown') AS [DB Name],
ecp.objtype AS [Object Type],
t.[text] AS [Adhoc Batch or Object Call],
SUBSTRING(t.[text], (qs.[statement_start_offset]/2) + 1,
((CASE qs.[statement_end_offset]
WHEN -1 THEN DATALENGTH(t.[text]) ELSE qs.[statement_end_offset] END
- qs.[statement_start_offset])/2) + 1) AS [Executed Statement]
,qs.[last_execution_time] AS [Last Exec Time]
,qs.[creation_time] AS [Creation Time]

FROM sys.dm_exec_query_stats AS qs
    JOIN sys.dm_exec_cached_plans ecp 
            ON qs.plan_handle = ecp.plan_handle
            CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
where
    ecp.objtype = 'Proc'

order by [Last Exec Time]  desc

这篇关于如何在 SQL Server 2008 中找到存储过程的最后修改日期、修改用户?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！