How to refresh the definition of a T-SQL user-defined function after a dependent object is redefined?

Question

Consider the following sequence of events:

1. A view v_Foo is defined
2. A user-defined function GetFoo() is defined that includes all columns from v_Foo (using 'Select * ...')
3. The definition of v_Foo changes and now includes more columns
4. I now want GetFoo() to include the new columns in v_Foo, but it still references the old definition

I can just re-run the script that created GetFoo in the first place and all will be well; but that's problematic for reasons I won't go into. Is there any other way to refresh the definition of a user-defined function so that it's in sync with its dependent objects?

Answer 1

Short, easy answer is No.

You have to redefine the RETURN TABLE statement in Tabular UDF, GetFoo()
whenever the definition of v_Foo changes.

But there is a way to get around it (translated as not practical).

1. Create a DDL trigger on ALTER_VIEW event.
2. Then use a dynamic SQL to create the GetFoo().

Answer 2

It would be nice to see the definition of the function. All you've said is it is using SELECT *. Can you be more specific?

You also forgot to tell us what version of SQL Server you are using. If >= 2005, have you looked at sp_refreshsqlmodule? http://technet.microsoft.com/en-us/library/bb326754.aspx http://technet.microsoft.com/en-us/library/bb326754%28SQL.90%29.aspx

Curious what your reasons are for insisting on SELECT *. Lots of discussion about it here, but the cons still outweigh the pros by a large margin, IMHO:

http://sqlblog.com/blogs/aaron%5Fbertrand/archive/2009/10/10/bad-habits-to-kick-using-select-omitting-the-column-list.aspx