SQL 查找调用函数的所有存储过程
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/2833389/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Finding all Stored procedures calling a function
提问by Abhishek Jain
How can I find out all the stored procedures that are calling a particular user defined function in SQL Server 2005.
如何找出在 SQL Server 2005 中调用特定用户定义函数的所有存储过程。
Or how to assign a defult value to a parameter in a user defined function so that when a stored procedure calls that function and does not pass any value to that parameter function assumes the default value.
或者如何为用户定义的函数中的参数分配默认值,以便当存储过程调用该函数并且不向该参数传递任何值时,函数采用默认值。
Regards, Abhishek jain
问候, 阿布舍克耆那教
回答by KM.
QUERY sys.sql_modules
use this procedure, where you pass in the function name:
QUERY sys.sql_modules
使用此过程,您在其中传递函数名称:
CREATE PROCEDURE dbo.Find_Text
@SearchValue nvarchar(500)
AS
SELECT DISTINCT
s.name+'.'+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
INNER JOIN sys.schemas s ON o.schema_id=s.schema_id
WHERE m.definition Like '%'+@SearchValue+'%'
--AND o.Type='P' --<uncomment if you only want to search procedures
ORDER BY 1
GO
This procedure searches within procedures, views, and functions for the given string. You can search for any string, not just function names. You can also include wild cards in the middle of the given search term.
此过程在过程、视图和函数中搜索给定字符串。您可以搜索任何字符串,而不仅仅是函数名称。您还可以在给定搜索词的中间包含通配符。
FUNCTION DEFAULTS
you can specify default values for function parameters. However, When a parameter of the function has a default value, the keyword DEFAULT must be specified when the function to retrieve the default value. This behavior is different from using parameters with default values in stored procedures in which omitting the parameter also implies the default value.
FUNCTION DEFAULTS
您可以指定函数参数的默认值。但是,当函数的参数有默认值时,函数检索默认值时必须指定关键字DEFAULT。这种行为不同于在存储过程中使用带有默认值的参数,在存储过程中省略参数也意味着默认值。
try it out:
试试看:
CREATE FUNCTION dbo.Just_Testing
(
@Param1 int
,@Param2 int=0
)
RETURNS varchar(100)
BEGIN
RETURN CONVERT(varchar(10),@Param1)+'-'+CONVERT(varchar(10),@Param2)
END
GO
PRINT 'hello world '+dbo.Just_Testing(2,default)+', '+dbo.Just_Testing(5,2)
GO
PRINT 'hello world '+dbo.Just_Testing(2 )+', '+dbo.Just_Testing(5,2)
OUTPUT:
输出:
hello world 2-0, 5-2
Msg 313, Level 16, State 2, Line 1
An insufficient number of arguments were supplied for the procedure or function dbo.Just_Testing.
But I'm guessing that you need to change a function, adding as parameter, and now need to fix it everywhere. This DEFAULT
would still be as much work, since you need to touch every call made to it.
但我猜你需要更改一个函数,添加为参数,现在需要在任何地方修复它。这DEFAULT
仍然需要做很多工作,因为您需要触摸每次调用它。
SP_DEPENDS
you can also use sp_depends (Transact-SQL)to find every usage of the function.
SP_DEPENDS
您还可以使用sp_depends (Transact-SQL)来查找该函数的每个用法。
回答by user1553525
declare @SearchValue as varchar(50)
set @SearchValue = 'GETUTCDATE'
SELECT DISTINCT
s.name+'.'+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
INNER JOIN sys.schemas s ON o.schema_id=s.schema_id
WHERE m.definition Like '%'+@SearchValue+'%'
AND o.Type='P' --<uncomment if you only want to search procedures
ORDER BY 1
回答by hgulyan
Just use this procedure to find any text in your stored procedures.
只需使用此过程查找存储过程中的任何文本。
CREATE PROCEDURE [dbo].[Find_Text_In_SP]
@StringToSearch varchar(100)
AS
SET @StringToSearch = '%' +@StringToSearch + '%'
SELECT Distinct SO.Name
FROM sysobjects SO (NOLOCK)
INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
AND SO.Type = 'P'
AND SC.Text LIKE @stringtosearch
ORDER BY SO.Name
回答by John Franklin
You need to be careful when using the SYSCOMMENTS table as suggested by hgulyan... That table has the definition of the object broken into multiple rows and could cause your search criteria to be missed if it was broken apart across two entries. In SQL 2005 and later you can use the SYSMODULES table instead. Run the following code to see the differences and look for occurrences where keywords (i.e. - possibly your search phrase) have been split across multiple lines when using the syscomments method...
使用 hgulyan 建议的 SYSCOMMENTS 表时,您需要小心...该表将对象的定义分成多行,如果将其拆分为两个条目,可能会导致您的搜索条件丢失。在 SQL 2005 及更高版本中,您可以改用 SYSMODULES 表。运行以下代码以查看差异并查找在使用 syscomments 方法时关键字(即 - 可能是您的搜索词组)被拆分为多行的情况...
SELECT TOP 1000 SO.NAME, SC.TEXT
FROM SYS.SYSOBJECTS SO
JOIN SYS.SYSCOMMENTS SC
ON SO.ID = SC.ID
WHERE SO.TYPE = 'P'
ORDER BY SO.NAME, SC.COLID
SELECT TOP 1000 SO.NAME, SM.DEFINITION
FROM SYS.SYSOBJECTS SO
JOIN SYS.SQL_MODULES SM
ON SO.ID = SM.[OBJECT_ID]
WHERE SO.TYPE = 'P'
ORDER BY SO.NAME
回答by E.J. Brennan
A 'low-tech' way to find all the stored procedures using a function is to use management studio to "generate scripts" for all the procs into a single file, and then use the editor window to search on the keywords you want to find.
使用函数查找所有存储过程的“低技术”方法是使用 management studio 为所有过程“生成脚本”到一个文件中,然后使用编辑器窗口搜索要查找的关键字.
回答by Dark Matter
Simply run this below query:
只需运行以下查询:
SELECT DISTINCT
s.name+'.'+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
INNER JOIN sys.schemas s ON o.schema_id=s.schema_id
WHERE m.definition Like '%FUNCTION_NAME%'
--AND o.Type='P' --<uncomment if you only want to search procedures
ORDER BY 1
And replace the FUNCTION_NAME with yours. That's it!!
并将 FUNCTION_NAME 替换为您的。就是这样!!