This blog is useful to all my friends who are working on the .Net Technology and wants to enhance their skills as well their problem solving ability.

Thursday, August 30, 2012

Compare Two database structure By Query only

This will give you the list of Columns along with Table Name, Column Name and Data Type that is in master but not exists or different from test.



SELECT  
    T.[name] AS [table_name], AC.[name] AS [column_name],  
        TY.[name] AS system_data_typeFROM    [master].sys.[tables] AS T  
    INNER JOIN [master].sys.[all_columns] AC ON T.[object_id] = AC.[object_id] 
    INNER JOIN [master].sys.[types] TY ON AC.[system_type_id] = TY.[system_type_id]
EXCEPT
SELECT  
    T.[name] AS [table_name], AC.[name] AS [column_name],  
        TY.[name] AS system_data_typeFROM    test.sys.[tables] AS T  
    INNER JOIN test.sys.[all_columns] AC ON T.[object_id] = AC.[object_id] 
    INNER JOIN test.sys.[types] TY ON AC.[system_type_id] = TY.[system_type_id]



http://dbcomparer.com/Download/Default.aspx