SQL Server 在哪里存储存储过程代码? [英] Where does SQL Server store the stored procedure code?

问题描述

我曾经需要存储过程的行来跟踪我是否引用了某个函数、过程或表，或者有时尝试在 sp 代码中查找某些内容.

I once needed the lines of the stored procedures to be able to trace whether I have a reference to some function, procedure or table, or sometimes to try to find something inside of the sp's code.

SQL Server 在哪里存储过程的代码?

Where does SQL Server store the procedure's code?

推荐答案

使用 sys.sql_modules 因为 definition 是 nvarchar(max) 因为它不会截断长代码.

Use sys.sql_modules because definition is nvarchar(max) because it will not truncate long code.

在 INFORMATION_SCHEMA.ROUTINES 中，ROUTINE_DEFINITION 列只有 nvarchar(4000) 所以如果你尝试查看一个长程序的文本并且你会看到它被截断了.

In INFORMATION_SCHEMA.ROUTINES the ROUTINE_DEFINITION column is only nvarchar(4000) so if you try view the text of a long procedure and you will see that it is truncated.

使用它来搜索任何过程、视图、函数中的文本:

Use this to search for text in any procedure, view, function:

SELECT DISTINCT
    o.name AS Object_Name,o.type_desc
    FROM sys.sql_modules        m 
        INNER JOIN sys.objects  o ON m.object_id=o.object_id
    WHERE m.definition Like '%'+@Search+'%'
    ORDER BY o.type_desc,o.name 

使用它来查看给定过程、视图、函数的文本:

use this to view the text of a given procedure, view, function:

select * from sys.sql_modules where object_id=object_id('YourProcedure')

这篇关于SQL Server 在哪里存储存储过程代码?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！