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

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?

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().

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?

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:

• Bad habits to kick : using SELECT * / omitting the column list