以返回值作为每一行的列值执行动态SQL
以返回值作为每一行的列值执行动态SQL
提问于 2019-05-24 17:58:35
我有一个相当简单的查询，我开始修改它，以便删除临时表，因为我们在许多不同的系统和客户端上都存在并发问题。
现在，简单的解决方案是在多个单独的查询中拆分查询，以复制SQL以前所做的事情。
我试图找出一种将动态SQL查询的结果作为列值返回的方法。新的查询非常简单，它查找具有特定格式和输出的所有表的系统对象。我所缺少的是，对于每条记录，我需要在每个表上输出动态查询的结果。
查询：
SELECT [name] as 'TableName'
FROM SYSOBJECTS WHERE xtype = 'U'
AND (CHARINDEX('_PCT', [name]) <> 0
OR CHARINDEX('_WHT', [name]) <> 0)所有这些表都有一个名为Result的公共列，它是一个浮点数。我要做的是在一些WHERE子句下返回这个列的计数，这个子句是泛型的，并且将运行所有表。
所需的查询(我知道它无效)是：
SELECT [name] as 'TableName',
sp_executesql 'SELECT COUNT(*) FROM ' + [name] + ' WHERE Result > 0 OR (Result < 139 AND CurrentIndex < 15)' as 'ResultValue'
FROM SYSOBJECTS WHERE xtype = 'U'
AND (CHARINDEX('_PCT', [name]) <> 0
OR CHARINDEX('_WHT', [name]) <> 0)在以前很容易的时候。我们有一个包含2列的临时表，并且首先填充表名。然后，我们在临时表上迭代并执行动态sql，并返回OUTPUT变量中的值，然后简单地更新临时表的记录，最后返回表。
我尝试过一个标量函数，但是它不支持动态SQL，所以它不能工作。我不想为13,000个表创建13,000~不同的查询。
我试过使用引用表并使用触发器来更新状态，但它使系统速度减慢了很多。平均表插入和删除2 800万条记录。由于索引非常好，最初的临时表查询只需5-6分钟即可执行，现在达到25-30分钟。
除了查询表列表，然后客户端逐一查询每个表以了解其状态之外，还有其他解决方案吗？
如果现在有一些新的特性，我们将使用Server 2017
回答 1
Stack Overflow用户
回答已采纳
发布于 2019-05-24 19:03:52
您可以将此脚本用于您的目的(在Server 2016中测试)。
更新：现在应该可以工作了，因为结果现在是一个集合。
EXEC sp_msforeachtable
@precommand = 'CREATE TABLE ##Statistics
(TableName varchar(128) NOT NULL,
NumOfRows int)',
@command1 ='INSERT INTO ##Statistics (TableName, NumOfRows)
SELECT ''?'' Table_Name, COUNT(*) Row_Count FROM ? WHERE Result > 0 OR (Result < 139 AND CurrentIndex < 15)',
@postcommand = 'SELECT TableName, NumOfRows FROM ##Statistics;
DROP TABLE ##Statistics'
,@whereand = ' And Object_id In (Select Object_id From sys.objects
Where name like ''%_PCT%'' OR name like ''%_WHT%'')'有关sp_msforeachtable的详细信息，请访问这链接
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/56297516
复制相关文章
相似问题
腾讯云开发者
