SQL Server 2008 Compare two different database tables for column name, data type and length

sql, sql-server-2008

Solution

Resolved it my self :

SELECT  
    T.[name] AS [table_name], AC.[name] AS [column_name],  
        TY.[name] AS system_data_type
FROM    [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_type
FROM    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]

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.

Problem

I am using SQL Server 2008 R2. I have created two different databases `Master` and `Test`. Both of them have the same tables and columns in the beginning, but then `master` is used for some stable environment and `test` is used by me for ongoing development. Now due to development I have changed some column's data types, added some new columns to the tables, deleted some columns, added some new tables and also deleted. Now I want to make `Master` same as `test`. I cannot drop and re-create `Master` as it contains some sensitive live data. So I need to compare each and every table of `master` with `test` along with column name, data type, constraints and length. Is there any solution?

Original source