比较实例中每个数据库的两个表的行数

Question

我有一个包含多个数据库的sql实例。在一个查询中，我想统计每个数据库中两个表(商店和位置(的行数，以便能够比较值。

到目前为止，我有以下查询：

SELECT ('SELECT COUNT(1) FROM [' + name + '].[abc].[Shops]') as shopCount, 
('SELECT COUNT(1) FROM [' + name + '].[def].[Locations]') as locationCount,
name as DB
FROM sys.databases 
WHERE OBJECT_ID('[' + name + '].[abc].[Shops]') IS NOT NULL AND 
OBJECT_ID('[' + name + '].[def].[Locations]' ) IS NOT NULL

这导致以下输出

shopCount                                      | locationsCount                                     | DB
------------------------------------------------------------------------------------------------------------------
SELECT COUNT(1) FROM [database1].[abc].[Shops] | SELECT COUNT(1) FROM [database1].[def].[Locations] | database1
------------------------------------------------------------------------------------------------------------------
SELECT COUNT(1) FROM [database2].[abc].[Shops] | SELECT COUNT(1) FROM [database2].[def].[Locations] | database2

很明显，我并没有将字符串作为查询来执行，但却不知道如何执行

Answer 1

类似这样的东西：

DECLARE @DynamicTSQLStatement NVARCHAR(MAX);
CREATE TABLE #DataSource
(
[shopCount] INT
,[locationCount] INT
,[database] SYSNAME
);
SET @DynamicTSQLStatement = STUFF
(
(
SELECT ';INSERT INTO  #DataSource SELECT (SELECT COUNT(1) FROM [' + name + '].[abc].[Shops]), (SELECT COUNT(1) FROM [' + name + '].[def].[Locations]), ''' + name +'''' 
FROM sys.databases          
WHERE OBJECT_ID('[' + name + '].[abc].[Shops]') IS NOT NULL AND 
OBJECT_ID('[' + name + '].[def].[Locations]' ) IS NOT NULL
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1
,1
,''
);

EXEC sp_executesql @DynamicTSQLStatement;
SELECT *
FROM #DataSource;
DROP TABLE #DataSource;

Answer 2

您正在尝试执行一些动态sql。请阅读以下内容：http://www.sqlservertutorial.net/sql-server-stored-procedures/sql-server-dynamic-sql/

第一个例子似乎是你想要的。