none
How to find Differences in Column Data types between tables in Two different Databases using TSQL

    Question

  •  

    I have Two Database that exist on Two seperate servers. The two database contain same schema and contains tables and columns of same name.  Some tables have slight differences in terms of data types or Data type lenght.

     

    For example if a Table on ServerA has a column named - CustomerSale with Varchar (100, Null) and a table on ServerB has a column named CustomerSale with Varchar (60, Null), how can i find if other columns have similar differences in all tables with the same name and columns in the two servers.

     

    I am using SQL Server 2005.  And the Two Servers are Linked Servers 

     

    What Script can i use to accomplish this task. Thanks

     

     


     

     

     

    Monday, April 21, 2008 1:26 AM

Answers

  • The most efficient way would be to use a 3rd party such as Red Gates' SQL Compare or Visual Studios for Database professionals.

     

    If you do not want to use 3rd party tools, you will have to restore a copy of the database and do direct query comparison between the information_schema.columns system view.

     

     

     

    Monday, April 21, 2008 1:51 AM
    Moderator

All replies

  • The most efficient way would be to use a 3rd party such as Red Gates' SQL Compare or Visual Studios for Database professionals.

     

    If you do not want to use 3rd party tools, you will have to restore a copy of the database and do direct query comparison between the information_schema.columns system view.

     

     

     

    Monday, April 21, 2008 1:51 AM
    Moderator
  •  

    'Red Gates' may be a good solution for you.  But, you can also use sql query to get what you want.

     

    Code Snippet

    select t.name, c.*

    from [database].sys.columns c

    join [database].sys.tables t on t.object_id = c.object_id

    where t.name = '[your table name]'

     

     

    execute the query will give you the length of columns and all information about the columns of your table.  Then, you would need to do whereclause to compare the tables from 2 different databases.

    Tuesday, April 22, 2008 3:08 PM
  • You can use tablediff Utility, that comes with the sql server 2005...


    "C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe" -sourceserver MyServer1
                                                                 -sourcedatabase MyDatabase1
                                                                 -sourcetable MyTable1
                                                                 -destinationserver MyServer2 
                                                                 -destinationdatabase MyDatabase2
                                                                 -destinationtable MyTable2
                                                                 -et DiffsTable

     


    More info on this cool tool in BOL here

    Tuesday, April 22, 2008 6:15 PM