Compare all tables row counts for 2 server instances?

Question

Compare all tables row counts for 2 server instances?

lan2022nguyen

SSC-Addicted

Points: 459

December 8, 2023 at 3:56 pm

USE [DBName]

SELECT
      QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]
      , SUM(sPTN.Rows) AS [RowCount]
FROM 
      sys.objects AS sOBJ
      INNER JOIN sys.partitions AS sPTN
            ON sOBJ.object_id = sPTN.object_id
WHERE
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
      AND index_id < 2 -- 0:Heap, 1:Clustered
GROUP BY 
      sOBJ.schema_id
      , sOBJ.name
ORDER BY [TableName]

I have the code above to get the row counts for all tables in the database. i can exec this for instanceA and instanceB. then export the results to excel and do a compare the differences.

is there an easier way to do this? goals is to check if all data are in sync for the database in both instances.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101078 More actions · Answer 1

You don't need to export to Excel to do the comparison, you can do it directly in SQL Server.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

lan2022nguyen SSC-Addicted Points: 459 More actions · Answer 2

ScottPletcher wrote:

You don't need to export to Excel to do the comparison, you can do it directly in SQL Server.

would you mind to elaborate more on this. i'm new and still learning. trying to automate as much as possible and avoid doing manual if i don't have to.

to clarify, i exec the query in 2 separate grid result because they are 2 difference instance.

are you saying i can write a t-sql to compare 2 grid results from a single query?

pietlinden SSC Guru Points: 63485 More actions · Answer 3

If an account can access both of the instances, you could do something like inserting the results from one call (but include the database name), and then the other call, so you'd end up with

and then just join on Db1TableName = Db2TableName AND <dbName1> != <dbName2>

Emperor100 SSCrazy Points: 2173 More actions · Answer 4

Use SSMS, Connect to two different instances/databases in separate query windows...

Go to Windows menu --> Click New Vertical Tab Group

=======================================================================