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

debugcn 发表于 Dev

安德里莫

我有一个包含许多数据库的sql实例。在一个查询中，我想计算每个数据库中Shops和Locations两个表的行数，以便能够比较这些值。

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

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

很显然，我没有将字符串作为查询执行，但无法弄清楚如何执行。

Gotqn

像这样：

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;

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。