IF OBJECT_ID('dbo.up_query','P') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.up_query;
END
GO
CREATE PROCEDURE dbo.up_query
@target NVARCHAR(MAX)
AS
SELECT
a.name,b.definition
FROM sys.all_objects a WITH (NOLOCK)
INNER JOIN sys.sql_modules b WITH (NOLOCK) ON a.object_id = b.object_id
WHERE b.definition LIKE '%' + @target + '%'
GO
BEGIN
DROP PROCEDURE dbo.up_query;
END
GO
CREATE PROCEDURE dbo.up_query
@target NVARCHAR(MAX)
AS
SELECT
a.name,b.definition
FROM sys.all_objects a WITH (NOLOCK)
INNER JOIN sys.sql_modules b WITH (NOLOCK) ON a.object_id = b.object_id
WHERE b.definition LIKE '%' + @target + '%'
GO
sys.all_objects
显示所有架构范围内的用户定义对象和系统对象的 UNION。
sys.sql_modules
返回每个对象都是一个 SQL 语言定义的模块中一行 SQL ServerSQL Server,包括本机编译标量用户定义函数。Returns a row for each object that is an SQL language-defined module in SQL ServerSQL Server, including natively compiled scalar user-defined function. 类型为 P、RF、V、TR、FN、IF、TF 和 R 的对象均有关联的 SQL 模块。Objects of type P, RF, V, TR, FN, IF, TF, and R have an associated SQL module. 在此视图中,独立的默认值,即 D 类型的对象也具有 SQL 模块定义。