CODEWITHSUNDEEP
sqlsql-server
AndriMO
AndriMO

Reputation: 13

Comparing row counts of two tables for each database in an instance

I have a single sql instance with many databases. In a single query I want to count the rows of two tables in each database, Shops and Locations, to be able to compare the values.

So far I have the following query:

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

Which results in the following output

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

So pretty obviously, I am not executing the strings as a query but am unable to figure out how to do so.

Upvotes: 0

Views: 342

Answers (2)

gotqn
gotqn

Reputation: 43666

Something like this:

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;

Upvotes: 1

Jason Aughenbaugh
Jason Aughenbaugh

Reputation: 11

You are trying to do some dynamic sql. Have a read here: http://www.sqlservertutorial.net/sql-server-stored-procedures/sql-server-dynamic-sql/

the first example seems to be what you're looking for.

Upvotes: 0

Related Questions