I need to change a function that already has dozens of dependencies. I'm not changing the input parameters or result of that function, only how it calculates its result. But even without changing the public interface of that function, trying to alter the function still gets an error that it cannot be altered because it has dependencies (mainly default constraints).
Is there a command modifier or tool to change that function without having to drop all its dependencies and re-creating them at the end (it will take me hours to write such script, knowing that I will probably have to repeat this process in a few weeks).
This was already asked over 10 years ago, without a solution, but I ask again because a lot has changed in 10 years in SQL Server, and I use a much modern version of it.
How do I alter a T-SQL FUNCTION with a dependent default constraint?
Wiki answer because fiddle seems down and i didn't wanna paste the whole shebang into a comment
If your function would call another function which does the actual calcs, it seems to avoid the issue.
create OR ALTER function dbo.Y_FUNC (
)
returns int
as
begin
return(0)
end
GO
create OR ALTER function dbo.X_FUNC (
)
returns int
as
begin
return(dbo.Y_FUNC())
end
GO
create table t_x (i int not null default dbo.X_FUNC())
go
CREATE OR alter function Y_FUNC (
)
returns INT
as
begin
return(1)
end
It might not help you this time, but next time you need to change stuff :)