SQL Show stored procedure details where contents of stored procedure uses a function name contained in a temp table

Question

I have a temporary table that contains a list of function names and I want to find all the stored procedures that mention one of those function names in the stored procedure code to be a new row in the query results.

This is what I've been trying:

--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%"

The last step has me stumped because instead of looking up "MyWord" I want to to check each result I have in the temporary table. I'm currently trying to see if I can get this with using a subquery but I'm not having much luck.

Are you *really* still using SQL Server 2008? 2008's support ended 18~ months ago. — Thom A, Mar 02 '21 at 16:32

score 0 · Accepted Answer · answered Mar 03 '21 at 17:04

I managed to resolve my problem, I took a bit of inspiration from here.

Create 2 temp tables, one for holding the function names and the other to hold the result of the end query.

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
)

Populate temp table with function names.

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

Loop through function names temp table and look to see if a function is being used in any stored procedures. If it is then add the result to the search results temp table.

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

Voila! It works and gives me the result that I wanted.

Personally, I'm not a huge fan of how I accomplished this, the reason for my opinion is because the use of the while loop the script won't be performant. I would appreciate any suggestions I can make to this script to improve the performance.

SQL Show stored procedure details where contents of stored procedure uses a function name contained in a temp table

1 Answers1