SQL显示存储过程的详细信息，其中存储过程的内容使用临时表中包含的函数名

Question

我有一个包含函数名列表的临时表，我想在存储过程代码中找到提到这些函数名之一的所有存储过程，并将其作为查询结果中的新行。

这就是我一直在尝试的:

--Gets list of functions and puts them into a new temp table.
Select *
into #functionList
from
(
SELECT name
FROM sys.sql_modules m 
INNER JOIN sys.objects o 
ON m.object_id=o.object_id
WHERE type_desc like '%function%'
) as myFunctionList
--The following SQL will get me a list of stored procedures where the contents of the stored procedure
--contains a word I want to look up.
SELECT DB_NAME() as DB_Name
,name AS Procedure_name 
,SCHEMA_NAME(schema_id) AS Schema_name
,Type_desc
,Create_date
,Modify_date
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE "%MyWord%"

最后一步把我难住了，因为我没有查找"MyWord"我想检查临时表中的每一个结果。我目前正试图看看我是否可以得到这个使用子查询，但我没有太多的运气。

Answer 1

我设法解决了我的问题，我从这里得到了一些灵感。

创建2个临时表，一个用于保存函数名，另一个用于保存结束查询的结果。

CREATE TABLE #FunctionList
(
FunctionID INT IDENTITY(1,1) PRIMARY KEY
,[Name] NVARCHAR(250) NOT NULL
)
CREATE TABLE #Results
(
FunctionID INT IDENTITY(1,1) primary key
,[DB_Name] NVARCHAR(250) NULL
,[Procedure_name] NVARCHAR(250) NULL
,[Schema_name] NVARCHAR(250) NULL
,[Type_desc] NVARCHAR(250) NULL
,[Create_date] DateTime NULL
,[Modify_date] DateTime NULL
)

用函数名填充临时表

INSERT INTO #FunctionList([Name])
SELECT [Name]
FROM
(
SELECT
[Name]
FROM sys.sql_modules m 
INNER JOIN sys.objects o ON m.object_id=o.object_id
WHERE type_desc LIKE '%function%'
) AS myFunctionList

遍历函数名临时表，查看是否在任何存储过程中使用了某个函数。如果是，则将结果添加到搜索结果临时表中。

DECLARE @Id INT --For the loop.
DECLARE @functionName NVARCHAR(250) --For the dynamic LIKE.
WHILE (SELECT Count(*) FROM #FunctionList) > 0
BEGIN
SELECT TOP 1 @Id = FunctionID FROM #FunctionList
SELECT TOP 1 @functionName = [Name] FROM #FunctionList
INSERT INTO #Results([DB_Name],[Procedure_name],[Schema_name],[Type_desc],[Create_date],[Modify_date])
SELECT
[DB_Name]
,[Procedure_name]
,[Schema_name]
,[Type_desc]
,[Create_date]
,[Modify_date]
FROM
(
SELECT DB_NAME() AS DB_Name
,[Name] AS [Procedure_name] 
,SCHEMA_NAME(schema_id) AS [Schema_name]
,[Type_desc] AS [Type_desc]
,[create_date] AS [create_date]
,[Modify_date] AS [Modify_date]
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%' + @functionName + '%'
) AS abc
Delete #FunctionList Where FunctionID = @Id --Next TOP 1 will be next row in Fn list.
End

瞧!它工作了，并给了我想要的结果。

就我个人而言，我不是一个超级粉丝我是如何完成的，我的意见的原因是，因为使用while循环的脚本将不会是性能。如果我能对这个脚本提出任何改进性能的建议，我将不胜感激。