Post reply

Comparing Queries for DBs on same Server

  • March 27, 2012 at 10:37 am

    #259204

    Hi Guys

    I have a simple query.

    I have a SQL db Server WHERE more than 300 DBs in work. Sometimes , developer needs to fix Live Issues and he does some changes in the One of the DBs without any intmation.

    Weekly , I get the report on >> which DB is in contrast/difference to which DB.

    Let me share one statement:

    The table(s) have the same name and different structure in the databases:

    Table Name = Table123

    Being a SCM Person , I want a short/Simple solution.

    I need a Simple Query where i just mention the DB Names(let 2 DBs) and their Tables to be compared , it outputs me just the difference.

    Objective: I would help me to Identify the Builds which are Missing here.

    I am looking forward of your ACK.

    If you have any Better idea than This , Kindly share.

    Thanks

    Waseem Bukhari

    CMer

  • March 27, 2012 at 10:42 am

    #1465142

    Did you have a look at redgates SQL-Compare[/url]?

    It might do exactly what you're looking for.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • March 27, 2012 at 10:49 am

    #1465157

    Thanks for your prompt reply.

    I know its better atleast But i want if it solved through Query.

  • March 27, 2012 at 11:06 am

    #1465174

    If they are on the same server you might can use checksum to detect differences in rows.

    Here is a simple example

    SELECT

    *

    FROM

    (SELECT KeyColumns, CHECKSUM(CheckColumn1, CheckColumn2, ...) Chk FROM DB1.dbo.TableName) X

    FULL OUTER JOIN

    (SELECT KeyColumns, CHECKSUM(CheckColumn1, CheckColumn2, ...) Chk FROM DB2.dbo.TableName) P

    ON

    P.KeyColumns = X.KeyColumns

    WHERE

    P.Chk != X.Chk

    That should accomplish your goal.

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

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