Refreshing metadata on user functions t-SQL

Question

I am doing some T-SQL programming and I have some Views defines on my database. The data model is still changing these days and I have some table functions defined. Sometimes i deliberately use

 select * from MYVIEW

in such a table function to return all columns. If the view changes (or table) the function crashes and I need to recompile it. I know it is in general good thing so that it prevents from hell lotta errors but still...

Is there a way to write such functions so the dont' blow up in my face everytime I change something on the underlying table? Or maybe I am doing something completely wrong...

Thanks for help

Answer 1

Define views as "WITH SCHEMABINDING"

And I'll refer you to my answer here which covers similar stuff...

“select * from table” vs “select colA,colB,etc from table” interesting behaviour in SqlServer2005

In this case, the problem is not the udf but how views behave without SCHEMABINDING

Edit: Cade Roux's sp_refreshsqlmodule might do the trick. I've never used it.

Answer 2

gbn's answer is best - but when you have SCHEMABINDING, this often prevents you from making underlying changes without first removing SCHEMABINDING and then replacing it when recreating the module. You can't use SCHEMABINDING if your object references objects outside the database.

If this difficulty is so great you don't wish to or can't use SCHEMABINDING, then using sp_refreshsqlmodule in some kind of regular process which you run to check your SQL modules for errors before they actually are used (it can be run on any non-schemabound view, UDF, stored proc, etc) is your friend.

You can use both techniques together - you cannot (and there is no need to) run sp_refreshsqlmodule against schemabound objects.

e.g., you can only run it on these modules:

SELECT *
FROM    INFORMATION_SCHEMA.ROUTINES
        WHERE   (
                 OBJECTPROPERTY(OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) + '.' + QUOTENAME(ROUTINE_NAME)), N'IsSchemaBound') IS NULL
                 OR OBJECTPROPERTY(OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) + '.' + QUOTENAME(ROUTINE_NAME)),
                                   N'IsSchemaBound') = 0
                )