Technical Article

Script to find a particular string in procs/func.

,

Requirement:
I added a parameter to an existing user defined function. This user-defined function was
used in many procedures across system. Whenever a parameter is added to a user defined function, then we will have to change the calling of the function and pass the value for the new parameter added. Even though the parameter is set default value as NULL. An explicit value will have to be passed to the new parameter. The same is true when you delete a parameter from the user-defined function. If the necessary changes are not done in calling of the function then it leads to an error.
Now since I added a parameter to the existing user-defined function, I wanted to find out the names of the stored procedures / functions wherever the modified function is called. So that I can pass an additional value to the new parameter added. Hence the procedure is created.
Solution: The UP_FindAWordUsedInSPOrFun is created which takes a single parameter @FindStr. It generates a list of procedures and functions wherever the matching string is found in any of the procedures or functions.
Eg.:
A function is initially created as
Create function UF_Function1 (Parameter1 Integer) ……
A procedure is created as
Create PROC UP_Proc1(…)
As
Begin
Select * from DBO.UF_Function1(2)
end

A function is CHANGED with an additional parameter as
Create function UF_Function1 (Parameter1 Integer, Paramter2 Integer) ……
Assuming you do not know the name of the procedure where you have used the function,
Executing a UP_FindAWordUsedInSPOrFun :
EXEC UP_FindAWordUsedInSPOrFun 'UF_Function1’
The output of the procedure would be as
Name ObjectType
UP_Proc1 PROCEDURE

One can also use the procedure to find any particular string that is used in any stored procedure or function.

CREATE PROCEDURE UP_FindAWordUsedInSPOrFun
 @FindStr AS VARCHAR(500)
AS
BEGIN
 SELECT DISTINCT NAME AS [NAME], 
 CASE WHEN TYPE ='P' THEN 'PROCEDURE'
      WHEN TYPE IN('FN', 'IF','TF') THEN 'FUNCTION'
   END AS OBJECTTYPE
 FROM SYSCOMMENTS comm
 inner join sysobjects obj 
 on comm.id = obj.id and obj.type IN ('P','FN', 'IF', 'TF')
 WHERE lower(TEXT) LIKE '%'
       + ltrim(rtrim(lower(@FindStr))) + '%'
END

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating