2011-10-18

Alter T-SQL Functions with Schema Binding

T-SQL likes to complain about Schema Binding for User Defined Functions... particularly when you try to alter the function.

You may have turned on Schema Binding for a few different reasons... I have used it for persisted calculated columns.

You might see an error message something similar to this.
Msg 3729, Level 16, State 3, Procedure ConvertToBase64, Line 13
Cannot ALTER 'dbo.' because it is being referenced by object ''.
The only answer that I've founded is to delete the columns, alter the function, and then re-add the columns. The following code will do that for you!


/*******************************************************************************************
drop dependent columns
********************************************************************************************/

DECLARE @FunctionName VARCHAR(50)
DECLARE @SQL VARCHAR(MAX)
DECLARE @ColumnCount INT
DECLARE @Counter INT
SET @FunctionName = ''

IF OBJECT_ID('tempdb..#ExecuteStatements') IS NULL
BEGIN
CREATE TABLE #ExecuteStatements
(
ID INT IDENTITY(1,1)
, DropStatement VARCHAR(MAX)
, AddStatement VARCHAR(MAX)
)
END


INSERT INTO #ExecuteStatements
(
DropStatement
, AddStatement
)
SELECT
'ALTER TABLE [' + sch.name + '].[' + tab.name + '] DROP COLUMN [' + col.name + ']'
, 'ALTER TABLE [' + sch.name + '].[' + tab.name + '] ADD [' + col.name + '] AS ' + col.definition + ' PERSISTED'
FROM sys.computed_columns col
JOIN sys.tables tab
ON tab.object_id = col.object_id
LEFT JOIN sys.schemas sch
ON sch.schema_id = tab.schema_id
WHERE definition LIKE '%' + @FunctionName + '%'

SELECT @ColumnCount = MAX(ID)
, @Counter = 1
FROM #ExecuteStatements

WHILE (@Counter <= @ColumnCount)
BEGIN
SELECT @SQL = DropStatement
FROM #ExecuteStatements
WHERE ID = @Counter

EXECUTE (@SQL)

SET @Counter = @Counter + 1
END
/*******************************************************************************************
END - drop dependent columns
********************************************************************************************/

GO



/*******************************************************************************************
Alter the function
********************************************************************************************/
ALTER FUNCTION [dbo].[]
(
    @InputString VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
WITH SCHEMABINDING
AS
BEGIN
    RETURN 'Banana:' + @InputString
END
GO
/*******************************************************************************************
END - Alter the function
********************************************************************************************/


/*******************************************************************************************
Readd any dropped columns
********************************************************************************************/
DECLARE @SQL VARCHAR(MAX)
DECLARE @ColumnCount INT
DECLARE @Counter INT

SELECT @ColumnCount = MAX(ID)
, @Counter = 1
FROM #ExecuteStatements

WHILE (@Counter <= @ColumnCount)
BEGIN
SELECT @SQL = AddStatement
FROM #ExecuteStatements
WHERE ID = @Counter

EXECUTE (@SQL)

SET @Counter = @Counter + 1
END

/*******************************************************************************************
END - Readd any dropped columns
********************************************************************************************/

-- Cleanup
--DROP TABLE #ExecuteStatements

No comments: