SQL server 存储过程常用查询

查询创建及修改日期

SELECT
a.name AS 存储过程名称,
a.create_date AS 创建日期,
a.modify_date AS 修改日期,
b.last_execution_time AS 最后执行日期,
b.execution_count AS 执行次数
FROM sys.procedures a
LEFT JOIN sys.dm_exec_procedure_stats b ON a.object_id = b.object_id AND b.database_id =8
WHERE a.is_ms_shipped =0 and a.name=‘Name’–去掉系统存储过程

查询所有存储过程及定义

SELECT name, definition

FROM sys.sql_modules AS m

INNER JOIN sys.all_objects AS o ON m.object_id = o.object_id

WHERE o.[type] = ‘P’

查询存储过程改动记录

select top
1000 [QS].[creation_time],
substring(
[ST].[text],
([QS].[statement_start_offset] / 2) + 1,
(
(case [QS].[statement_end_offset] when - 1 then datalength([st].[text]) else [QS].[statement_end_offset] end -
[QS].[statement_start_offset]) / 2
) + 1
) as [statement_text],
[ST].[text],
[QS].[total_worker_time],
[QS].[last_worker_time],
[QS].[max_worker_time],
[QS].[min_worker_time]
from [sys].[dm_exec_query_stats] [QS]
cross apply [sys].dm_exec_sql_text [ST]
– 时间段设置
where [QS].[creation_time] between ‘2021-12-10 00:00:00’
and ‘2022-04-29 23:59:00’
– SQL 语句模糊匹配，如insert、SELECT、UPDATE、DELETE
and [ST].[text] like ‘%DELETE%’
order by [QS].[creation_time] desc