SQL Server 查询存储过程信息

您所在的位置：网站首页 › dbvisualizer查看存储过程内容 › SQL Server 查询存储过程信息

SQL Server 查询存储过程信息

2024-05-31 19:10| 来源: 网络整理| 查看: 265

查询存储过程定义

–使用语句查看一个存储过程的定义

EXEC sp_helptext 'sp_name'//实际使用时将 sp_name 替换为具体存储过程名称

或

SELECT definition FROM sys.sql_modules WHERE object_id=OBJECT_ID('sp_name','P')

–查询所有存储过程的名称以及定义

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 obj.Name 存储过程名, sc.TEXT 存储过程内容 FROM syscomments sc INNER JOIN sysobjects obj ON sc.Id = obj.ID WHERE sc.TEXT LIKE '%Rebuild%' order by 1

查询存储过程所属 Job

Sa用户执行

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'

查询存储过程执行历史

Sa用户执行

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

或

SELECT DB_NAME(ISNULL(EPS.database_id,'')) [数据库名称] --ISNULL(DBS.name, '') AS DatabaseName ,OBJECT_NAME(EPS.object_id, EPS.database_id) [存储过程名称] --AS ObjectName ,EPS.cached_time [添加到缓存的时间]--AS CachedTime ,EPS.last_elapsed_time '最近执行所耗费时间（微秒）'--AS LastElapsedTime ,EPS.last_worker_time '上次执行存储过程所用的CPU时间(微秒)' ,EPS.execution_count [上次编译以来所执行的次数]--AS ExecutionCount ,EPS.total_worker_time / EPS.execution_count [平均每次执行所用的CPU时间总量(微秒)]--AS AvgWorkerTime ,EPS.total_elapsed_time / EPS.execution_count [平均每次执行所用的时间(微秒)]--AS AvgElapsedTime ,(EPS.total_logical_reads + EPS.total_logical_writes) / EPS.execution_count AS AvgLogicalIO ,b.text [存储过程内容] FROM sys.dm_exec_procedure_stats AS EPS CROSS APPLY sys.dm_exec_sql_text(EPS.sql_handle) b ORDER BY OBJECT_NAME(EPS.object_id, EPS.database_id) DESC;

参考：https://blog.csdn.net/xiaoye1202/article/details/84135391

【本文地址】

SQL Server 查询存储过程信息

SQL Server 查询存储过程信息

今日新闻

推荐新闻