none
How to compare two table's Columns of same server but in different databases. RRS feed

  • Question

  • Hi,

    Please suggest me a query in the sql server to find out difference between columns in the two tables.Also i need to identify for the foreign keys in both the table. Suppose i have two tables named A and B. Table A is in Database "Source" and table B is in Database "Destination".Now i have to write a query which iterete through table A and B and get the difference between these two tables Column and also keys.

    Please help me.

    Thanks in advance.

    Thursday, July 21, 2011 1:41 PM

Answers

  • their is no stright forward solution for this in SQL Server but I would suggest you to look for third party tools like Red gate -SQL compare.

    http://uk.linkedin.com/in/ramjaddu
    • Proposed as answer by Surendra Nath GM Thursday, July 21, 2011 3:02 PM
    • Marked as answer by KJian_ Friday, July 29, 2011 8:43 AM
    Thursday, July 21, 2011 1:46 PM
  • If it is only for the Data compare, you can use the Merge statement, in the SQL Server 2008.

    For comparing the keys, that do exists in one and do not in another, As RamJaddu said I am not aware of any solution in SQL server. Although we can write a customized code by using the sysobjects table like the below, a manual review needs to be done after that.

     

    -- The below code gives the forieghn keys / primary keys defined only in one table.....
    select name,count(1) From sysobjects where xtype in ('PK' ,'F')
    group by object_name(parent_obj)
    having count(1) = 1
    
    
    

    • Proposed as answer by Surendra Nath GM Thursday, July 21, 2011 3:01 PM
    • Marked as answer by KJian_ Friday, July 29, 2011 8:43 AM
    Thursday, July 21, 2011 1:53 PM
  • You can use the EXCEPT operator to find the difference:

    http://www.sqlusa.com/bestpractices2008/compare-tables/

     

    List all PRIMARY and FOREIGN KEYs:

    http://www.sqlusa.com/bestpractices2005/primaryandforeignkeys/

     


    Kalman Toth, SQL Server & Business Intelligence Training; sqlusa.com
    • Marked as answer by KJian_ Friday, July 29, 2011 8:43 AM
    Wednesday, July 27, 2011 4:36 AM
    Moderator

All replies

  • their is no stright forward solution for this in SQL Server but I would suggest you to look for third party tools like Red gate -SQL compare.

    http://uk.linkedin.com/in/ramjaddu
    • Proposed as answer by Surendra Nath GM Thursday, July 21, 2011 3:02 PM
    • Marked as answer by KJian_ Friday, July 29, 2011 8:43 AM
    Thursday, July 21, 2011 1:46 PM
  • If it is only for the Data compare, you can use the Merge statement, in the SQL Server 2008.

    For comparing the keys, that do exists in one and do not in another, As RamJaddu said I am not aware of any solution in SQL server. Although we can write a customized code by using the sysobjects table like the below, a manual review needs to be done after that.

     

    -- The below code gives the forieghn keys / primary keys defined only in one table.....
    select name,count(1) From sysobjects where xtype in ('PK' ,'F')
    group by object_name(parent_obj)
    having count(1) = 1
    
    
    

    • Proposed as answer by Surendra Nath GM Thursday, July 21, 2011 3:01 PM
    • Marked as answer by KJian_ Friday, July 29, 2011 8:43 AM
    Thursday, July 21, 2011 1:53 PM
  • You can use the EXCEPT operator to find the difference:

    http://www.sqlusa.com/bestpractices2008/compare-tables/

     

    List all PRIMARY and FOREIGN KEYs:

    http://www.sqlusa.com/bestpractices2005/primaryandforeignkeys/

     


    Kalman Toth, SQL Server & Business Intelligence Training; sqlusa.com
    • Marked as answer by KJian_ Friday, July 29, 2011 8:43 AM
    Wednesday, July 27, 2011 4:36 AM
    Moderator
  • You can use the below tsql to get the details of Columns & Indexes on any table in the current DB scope -

    USE DBName
    GO
    
    DECLARE @TableName AS VARCHAR(25) = 'AssorterOpinions'
    
    SELECT 
     C.* 
    FROM 
     sys.columns C
    INNER JOIN sys.tables T
     ON T.object_id = C.object_id 
    WHERE 
     T.name = @TableName
     
    SELECT 
     I.* 
    FROM 
     sys.indexes I
    INNER JOIN sys.tables T
     ON T.object_id = I.object_id 
    WHERE 
     T.name = @TableName
     
    
    


    By using the above tsql, you can get the required data of the 2 tables one by one and then persist it temporarily. Once the complete data is persisted, just compare them as per your need.

     


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia

    Wednesday, July 27, 2011 4:58 AM