SQL sp_MSforeachdb：只包括来自有结果的数据库的结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20131930/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
sp_MSforeachdb: only include results from databases with results
提问by mustang888
I'm running the below stored procedure sp_MSforeachdbwith a simple command. My question is how to limit the result to show only the databases that have at least 1 record satisfying the command:
我正在sp_MSforeachdb使用一个简单的命令运行以下存储过程。我的问题是如何将结果限制为仅显示至少有 1 条记录满足命令的数据库：
Here's my stored procedure:
这是我的存储过程：
EXECUTE master.sys.sp_MSforeachdb 'USE [?];
IF (EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = ''Tabs''))
BEGIN
SELECT ''?'' as dbname,T.TabName, T.TabPath
FROM Tabs T
WHERE T.TabID IN (
SELECT Distinct TM.TabID
FROM TabModules TM
WHERE mID IN (
...
)
)
ORDER BY T.TabName
END
'
Any ideas how I can modify the sp so that it doesn't display the databases that have empty results (see image)?
任何想法如何修改 sp 以便它不显示具有空结果的数据库（见图）？
回答by Aaron Bertrand
Well, first, stop using sp_MSforEachDb. Oh, the problems (if you want proof, see here).
好吧，首先，停止使用sp_MSforEachDb. 哦，问题（如果你想要证据，请看这里）。
How about:
怎么样：
DECLARE @cmd NVARCHAR(MAX) = N'', @sql NVARCHAR(MAX) = N'';
SELECT @cmd += N'IF EXISTS (SELECT 1 FROM '
+ QUOTENAME(name) + '.sys.tables WHERE name = N''Tabs'')
SET @sql += N''UNION ALL
SELECT ''''' + name + ''''',T.TabName
FROM ' + QUOTENAME(name) + '.dbo.Tabs AS T
WHERE EXISTS
(
SELECT 1 FROM ' + QUOTENAME(name) + '.dbo.TabModules AS TM
WHERE TM.TabID = T.TabID
AND TM.mID IN -- this should probably be exists too
(
...
)
)
'''
FROM sys.databases
WHERE state = 0 -- assume you only want online databases
AND database_id > 4; -- assume you don't want system dbs
EXEC sp_executesql @cmd, N'@sql NVARCHAR(MAX) OUTPUT', @sql OUTPUT;
SET @sql = STUFF(@sql, 1, 10, '') + N' ORDER BY TabName;';
PRINT @sql; -- this will appear truncated, but trust me, it is not truncated
-- EXEC sp_executesql @sql;
If you really want some unknown, arbitrary number of separate resultsets, the change is simple.
如果您真的想要一些未知的、任意数量的单独结果集，更改很简单。
DECLARE @cmd NVARCHAR(MAX) = N'', @sql NVARCHAR(MAX) = N'';
SELECT @cmd += N'IF EXISTS (SELECT 1 FROM '
+ QUOTENAME(name) + '.sys.tables WHERE name = N''Tabs'')
SET @sql += N''SELECT ''''' + name + ''''',T.TabName
FROM ' + QUOTENAME(name) + '.dbo.Tabs AS T
WHERE EXISTS
(
SELECT 1 FROM ' + QUOTENAME(name) + '.dbo.TabModules AS TM
WHERE TM.TabID = T.TabID
AND TM.mID IN -- this should probably be exists too
(
...
)
)
ORDER BY T.TabName;
'';'
FROM sys.databases
WHERE state = 0 -- assume you only want online databases
--AND database_id > 4; -- assume you don't want system dbs
EXEC sp_executesql @cmd, N'@sql NVARCHAR(MAX) OUTPUT', @sql OUTPUT;
PRINT @sql; -- this will appear truncated, but trust me, it is not truncated
-- EXEC sp_executesql @sql;
回答by Ali
You basically need another IF to only run the select if data exists. Here's what i did to test.
您基本上需要另一个 IF 才能仅在数据存在时运行选择。这是我所做的测试。
On DB1:
在 DB1 上：
create table mytesttable(a int)
insert mytesttable values(1)
On DB2:
在 DB2 上：
create table mytesttable(a int)
So you want DB1 to return results, but DB2 not to. you can use the following sql:
所以您希望 DB1 返回结果，但 DB2 不这样做。您可以使用以下sql：
EXECUTE master.sys.sp_MSforeachdb 'USE [?];
IF (EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = ''mytesttable''))
BEGIN
IF EXISTS (SELECT 1 FROM mytesttable) BEGIN
SELECT ''?'' as dbname,T.A
FROM mytesttable AS T
END
END
'
This only returns: db1, 1
这只返回：db1, 1
