刷新用户函数t-SQL的元数据

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

我正在做一些T-SQL编程，我在我的数据库上有一些Views定义。这些天数据模型仍在改变，我定义了一些表函数。有时我故意使用

 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

感谢帮助

2 个解决方案

#1

Define views as "WITH SCHEMABINDING"

将视图定义为“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

“从表中选择*”vs“从表中选择colA，colB等”SqlServer2005中的有趣行为

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

在这种情况下，问题不是udf，而是如果没有SCHEMABINDING，视图的行为方式

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

编辑：Cade Roux的sp_refreshsqlmodule可能会解决问题。我从来没用过它。

#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.

gbn的答案是最好的 - 但是当你有SCHEMABINDING时，这通常会阻止你在没有先删除SCHEMABINDING然后在重新创建模块时替换它的情况下进行底层更改。如果对象引用数据库外部的对象，则不能使用SCHEMABINDING。

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.

如果这个难度太大你不希望或不能使用SCHEMABINDING，那么在你运行的某种常规进程中使用sp_refreshsqlmodule来检查你的SQL模块在实际使用之前是否有错误（它可以在任何非架构视图，UDF，存储过程等）是你的朋友。

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

您可以同时使用这两种技术 - 您不能（并且不需要）针对模式绑定对象运行sp_refreshsqlmodule。

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
                )

#1