SQLSERVER 查存储过程和作业的内容及历史执行情况

--查存储过程包含的内容：

 

SELECT obj.Name 存储过程名, sc.TEXT 存储过程内容

FROM syscomments sc

INNER JOIN sysobjects obj ON sc.Id = obj.ID

WHERE sc.TEXT LIKE '%NuctLD%'
order by 1

-- 根据SP来检查用的那个作业
SELECT *
FROM msdb.dbo.sysjobs JOB WITH( NOLOCK)
INNER JOIN msdb. dbo.sysjobsteps STP WITH(NOLOCK )
ON STP .job_id = JOB .job_id
WHERE STP .command LIKE N'% sp_name %'

 

--查存储过程的依赖

SELECT OBJECT_SCHEMA_NAME ( referencing_id ) AS referencing_schema_name,  
    OBJECT_NAME(referencing_id) AS referencing_entity_name,   
    o.type_desc AS referencing_desciption,   
    COALESCE(COL_NAME(referencing_id, referencing_minor_id), '(n/a)') AS referencing_minor_id,   
    referencing_class_desc, referenced_class_desc,  
    referenced_server_name, referenced_database_name, referenced_schema_name,  
    referenced_entity_name,   
    COALESCE(COL_NAME(referenced_id, referenced_minor_id), '(n/a)') AS referenced_column_name,  
    is_caller_dependent, is_ambiguous  
FROM sys.sql_expression_dependencies AS sed  
INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id  
where OBJECT_NAME(referencing_id) =N'sp_getDeliveryBill'

--查存储过程的执行情况：

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 --去掉系统存储过程