Recompile all Udf's associated with a Table

CREATE PROCEDURE [dbo].[usp_Maint_udf_Compile_For_Table]
(@TableName sysname)
AS
/* 
Name:usp_Maint_udf_Compile_For_Table
Author:Mark Hickey 
Date Created:05/09/2002 
Description:Stored Procedure used to get all UDF's associated with a table to re-complie
Amendment History: 
~~~~~~~~~~~~~ 
DateWhoWhy 
05/09/2002Mark HickeyInitial creation. 
*/ 

DECLARE @TABLE_ID INTEGER
SELECT 
@TABLE_ID = id
FROM 
sysobjects 
WHERE 
sysobjects.xtype ='u'
AND 
sysobjects.name = @TableName

DECLARE @UDF_OBJECT_NAME as nvarchar(128)
DECLARE @SQL_CODE as nvarchar(200)

DECLARE CursorUDF CURSOR FOR 
SELECT 
DISTINCT(sysobjects.name)
FROM
sysdepends
INNER JOIN
sysobjects
ON
sysobjects.id = sysdepends.id
WHERE
(sysobjects.xtype= 'if' or sysobjects.xtype = 'fn')  
AND
sysdepends.depid = @TABLE_ID



OPEN CursorUDF


FETCH NEXT FROM CursorUDF INTO  @UDF_OBJECT_NAME
IF @@FETCH_STATUS = 0
BEGIN
WHILE @@FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch succeeds.
IF @@FETCH_STATUS = 0
BEGIN

SET @SQL_CODE =  'SP_RECOMPILE '+@UDF_OBJECT_NAME
EXEC (@SQL_CODE)

END

--Empty the Bufer of Contents
SET @UDF_OBJECT_NAME = ''
SET @SQL_CODE = ''

--Obtain the Next Record and Place it in the Buffer parameter
FETCH NEXT FROM CursorUDF INTO  @UDF_OBJECT_NAME
END

  
END

CLOSE CursorUDF
DEALLOCATE CursorUDF
GO