如何跨数据库统计匹配指定名称的正在运行的存储过程数量

核心修改逻辑 #

• 利用sys.dm_exec_sql_text返回的dbid字段筛选目标库ScheduledJobs的执行请求，过滤其他库的无关数据
• 调用OBJECT_NAME函数时传入第二个参数database_id，指定从目标库的元数据中获取存储过程名称，解决该函数默认取当前库对象名导致的跨库匹配失效问题
• 修正原有存储过程中@@ROWCOUNT赋值位置错误的问题：原有逻辑将赋值写在存储过程的BEGIN END块外，无法正确取到SELECT语句的影响行数

修改后完整代码 #

USE [MySampleDB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[sp_CheckRuns2]
  @RowsAffected INT OUTPUT
AS
BEGIN
  -- 预获取目标库ScheduledJobs的数据库ID
  DECLARE @TargetDBId INT = DB_ID('ScheduledJobs')

  SELECT
    OBJECT_NAME(st.objectid, st.dbid) as ProcName
  FROM
    sys.dm_exec_connections as qs 
  CROSS APPLY sys.dm_exec_sql_text(qs.most_recent_sql_handle) st 
  WHERE
    st.dbid = @TargetDBId
    AND st.objectid IS NOT NULL
    AND OBJECT_NAME(st.objectid, st.dbid) LIKE '%sp_UPDATER'

  SELECT @RowsAffected = @@ROWCOUNT
  RETURN @RowsAffected
END
GO

权限要求 #

• 执行该存储过程的账号需要拥有VIEW SERVER STATE服务器级权限，才能正常访问sys.dm_exec_connections和sys.dm_exec_sql_text动态管理视图
• 执行账号需要拥有ScheduledJobs库的VIEW DEFINITION权限，才能正确解析目标库的存储过程名称

内容的提问来源于stack exchange，提问作者Alex A