Script that pull the names of all the stored procedures that has functions in the where clause?

Question

Can any one get me the Script that pull the names of all the stored procedures that has functions in the where clause?

For example ISNULL(fieldname,0)

you want to show store procedure name based on contents it has in where clause ? — Ameya Deshpande, Jan 14 '15 at 06:37
i want the list of all the Store Procedures in a database if they are using any inbuild functions in the where clause. — Ramachandra, Jan 14 '15 at 06:50
Why it is so important? Do you want to search selected database or all databases? Which version of MS SQL Server? Do you want to use only SQL or any programming language? — Maciej Los, Jan 14 '15 at 06:55
Have you seen this: http://stackoverflow.com/questions/5079457/how-do-i-find-a-stored-procedure-containing-text ? — Maciej Los, Jan 14 '15 at 06:57
I need it for perfomance tuning. I need only on selected database. Using SQl Sever 2012. — Ramachandra, Jan 14 '15 at 06:57
I guess you want to avoid using functions in WHERE clause for better performance — sqluser, Jan 14 '15 at 07:01

score 1 · Answer 1 · answered Jan 14 '15 at 06:59

Really hard to find

My first attempt is to run something like this

SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE' 
AND ROUTINE_DEFINITION LIKE '%WHERE%ISNULL%'

But you need to play with all functions. Better to join this query with a table contacting all built-in functions.

score 0 · Answer 2 · edited May 23 '17 at 11:50

If am not wrong we cannot take the list of system functions. refer this Extracting system functions in SQL Server 2008 R2

if the list is of user defined functions, you can try this way

SELECT ROUTINE_NAME
FROM   INFORMATION_SCHEMA.ROUTINES A
       JOIN SYS.OBJECTS B
         ON A.ROUTINE_DEFINITION LIKE '%' + B.NAME + '%'
            AND B.TYPE IN ( 'FN', 'IF', 'TF' )
            AND A.ROUTINE_TYPE = 'PROCEDURE'

score 0 · Answer 3 · answered Jan 14 '15 at 08:07

Below is one of the way to check function that utilized in stored procedures

DECLARE @nStart INT = 1
DECLARE @nEnd INT = (SELECT COUNT(1) FROM SYS.SYSOBJECTS WHERE XTYPE='TF'OR XTYPE='FN')
DECLARE @Query NVARCHAR(MAX) = ''
DECLARE @sUnion NVARCHAR(30) = ' UNION ALL '
DECLARE @FncName NVARCHAR(100)

WHILE @nStart<=@nEnd
BEGIN

    SET @FncName = (SELECT NAME FROM (SELECT SO.NAME,ROW_NUMBER() OVER (ORDER BY SO.NAME) RN FROM SYS.SYSCOMMENTS SC INNER JOIN SYS.SYSOBJECTS SO
                            ON SO.ID = SC.ID WHERE SO.XTYPE = 'TF' OR SO.XTYPE = 'FN')T1 WHERE RN = @nStart)

    SET @Query =  @Query + ' SELECT ' + '''' + @FncName  + '''' 

    IF(@nStart!=@nEnd)
        BEGIN
            SET @Query  = @Query + @sUnion
        END

    SET @nStart = @nStart + 1
END

DECLARE @TempTable TABLE
(
ID INT IDENTITY(1,1),
TITLE NVARCHAR(MAX)
)

INSERT INTO @TempTable
EXEC SP_EXECUTESQL @Query

DECLARE @nTempTableStart INT = 1
DECLARE @nTempTableEnd INT = (SELECT COUNT(1) FROM @TempTable)
DECLARE @Condition NVARCHAR(MAX) = ' '
DECLARE @FunctionName NVARCHAR(100)

WHILE @nTempTableStart <= @nTempTableEnd
BEGIN

    SET @FunctionName = (SELECT NAME FROM (SELECT TITLE 'NAME',ROW_NUMBER() OVER (ORDER BY TITLE) RN FROM @TempTable)T1 WHERE RN = @nTempTableStart)


    SET @Condition = @Condition + ' LIKE ' + '''' + '%'+@FunctionName+'%' + ''''

    IF @nTempTableStart!=@nTempTableEnd
        SET @Condition = @Condition + ' OR SC.TEXT'

    IF @nTempTableStart = @nTempTableEnd
        SET @Condition = @Condition + ' ) '

    SET @nTempTableStart = @nTempTableStart + 1
END


DECLARE @FinalQuery NVARCHAR(MAX)

SET @FinalQuery = 'SELECT SO.NAME,SC.TEXT FROM
SYS.SYSCOMMENTS SC INNER JOIN SYS.SYSOBJECTS SO ON SC.ID = SO.ID WHERE (SC.TEXT ' + @Condition
+ ' AND (SO.XTYPE !=''TF'' AND SO.XTYPE !=''FN'')'


EXECUTE SP_EXECUTESQL @FinalQuery

Script that pull the names of all the stored procedures that has functions in the where clause?

3 Answers3