Compare all tables row counts for 2 server instances?

  • 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.

  • 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) A socialist is someone who will give you the shirt off *someone else's* back.

  • 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?

  • 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

    <database>, <table name>, <size>

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

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

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

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

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

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