如何检测和收集sql-server tvf函数的底层架构中的更改

Question

假设我有一个内联-tvf函数(假设名称: cfn_test)，定义如下：

从TableA中选择*；

现在，在创建时，TableA的结构是：

colA，colB (此时数据类型并不重要)

让我们也添加一些数据：

| colA | colB |
|   1  |  2   |

应用程序使用"SELECT * FROM cfn_test()"，并获取：

| colA | colB |
|   1  |  2   |

现在问题是：

tableA的结构发生了变化，在colA和colB.Data之间插入了一个colC：

| colA | colC | colB |
|   1  |   3  |   2  |

应用程序现在获得：

| colA | colB |
|   1  |  3   |

如果我运行SSMS生成的alter函数脚本，没有任何更改，它现在返回正确的结果。

我的问题是：( a)是否可以根据文本中存在的"select *“(我认为这是错误行为的罪魁祸首)来检测哪些函数需要”更新“

( b)如果我设计一个循环遍历所有现有函数的脚本(使用INFORMATION_SCHEMA.ROUTINES )，我是否可以以某种方式自动生成SSMS生成并运行的"alter“脚本，以便用当前的底层模式更新所有函数？

Answer 1

A)您可以使用系统视图- sys.sql_modules或sys.sql_dependencies

在sql_modules中，您可以搜索对象的定义(函数、过程、视图)并找到所需的模式。也许是这样：

SELECT * FROM sys.sql_modules
WHERE definition LIKE '%TableA%' AND definition LIKE '%SELECT *%'

sys.sql_dependencies显示对象之间的依赖关系。因此，在更改TableA之后，您可以很容易地找到依赖于您的所有对象。可能会给你带来比你需要的更多的结果。

SELECT * FROM sys.sql_dependencies 
WHERE referenced_major_id = object_id('TableA')

无论哪种方式，您都可以在sys.objects上加入它，只获取函数的名称和筛选器(我会包括视图，因为它们在底层对象更改后也需要刷新)。就像这样：

SELECT o.name FROM sys.sql_dependencies d
LEFT JOIN sys.objects o ON d.object_id = o.object_id
WHERE referenced_major_id = object_id('TableA')
AND o.type in ('FN', 'IF', 'TF', 'FS', 'FT', 'V')

B)在找到函数名之后，以及在了解了用于刷新依赖对象的sp_refreshsqlmodule过程之后，应该不难生成脚本。

最简单的方法可能是为每个用户生成EXEC，然后复制/粘贴结果并运行它：

SELECT 'EXEC sp_refreshsqlmodule ''' +  o.name + '''' FROM sys.sql_dependencies d
LEFT JOIN sys.objects o ON d.object_id = o.object_id
WHERE referenced_major_id = object_id('TableA')
AND o.type in ('FN', 'IF', 'TF', 'FS', 'FT', 'V')

如果您想要更自动化，循环，连接单个脚本并运行它。希望你能处理好。