Technical Article

Recompile all Udf's associated with a Table

,

When altering the structure of tables it is good practice to run SP_RECOMPILE against the table to force triggers and stored procedures to recompile. However this does not incude Udf's which also rely on the table, and this means the Udf's may fail when they are called. Run this script and supply the table name to it to force the recompile of 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

Rate

Share

Share

Rate