表值函数刷新 [英] Table-valued function refresh
问题描述
我在SQL Server中有一个表值函数(TVF),如下所示:
创建函数TVF_xyz(@ AuditKey INT)
返回表
AS
返回
选择*
从xyz
WHERE AUDIT_KEY = @AuditKey
GO
现在,我在 xyz
表中添加了新列。 / p>
当我使用 TVF_xyz
查询时,它不会显示新列(显示所有其他列,但新添加的列)。
查询:
选择前10位*
FROM TVF_xyz(1543)
我想知道如何刷新TVF以显示新列。
PS:选择*
在TVF中用于提取所有列。
经过一番搜索,我发现了 sp_refreshsqlmodule(Transact-SQL),它是TVF的常见行为。
为了刷新TVF,以下需要执行SP:
EXEC sys.sp_refreshsqlmodule'TVF_xyz'
I have a table-valued function (TVF) in SQL Server that looks like this:
CREATE FUNCTION TVF_xyz(@AuditKey INT)
RETURNS TABLE
AS
RETURN
SELECT *
FROM xyz
WHERE AUDIT_KEY = @AuditKey
GO
Now, I added a new column to the xyz
table.
When I query using TVF_xyz
, it doesn't show me the new column (shows all other columns except newly added).
Query:
SELECT TOP 10 *
FROM TVF_xyz (1543)
I would like to know, how to refresh TVF to show new column.
PS: Select *
used in TVF to fetch all columns.
After bit of searching, I found sp_refreshsqlmodule (Transact-SQL), its common behavior of TVF.
In order to refresh TVF, following SP needs to be executed:
EXEC sys.sp_refreshsqlmodule 'TVF_xyz'
这篇关于表值函数刷新的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!