C # and SQL Server 2008 - script differences between dev and production database?

Question

I have a database that I use for all my development and testing, and a second database, which serves as my production database. What is the best way for a script to distinguish between tables (structure only) between them? My Dev database may have new columns that my products do not have, and I need to get differences in the production part without (obviously) affecting the data in the production database.

Answer 1

Many ways to do this, but one of the most popular is a tool like Red-Gate SQL Compare (www.red-gate.com). Our development team will be lost without such a tool.

Answer 2

If you are using Visual Studio 2010 Ultimate / Premium Edition or Visual Studio Team / Database Edition 2008, you will find everything you need in Visual Studio. (See Processing Changes in a Database Schema )

If you cannot VS2010 Ultimate or Premium, and not VS2008 Team or Database Editions, I would recommend looking at some products at http://www.red-gate.com/ (for example, http://www.red-gate.com/ products / SQL_Compare / index.htm ) with private functions.

Answer 3

ALTER/CREATE, dev, push to prod.

. RedGate Compare - ! MS TFS DB , RedGate , IMO.

Answer 4

Red Gate SQL Compare. .

Answer 5

( ) . dev. , , , . , ( ).

Answer 6

You can use system views to query this data. The following example assumes that your databases live on the same server. Essentially, you make a sub-query for each set of table / column / datatype and concatenate them by the name of the table and column. Any differences will be confirmed NULL on both sides of the connection. If you really want to get smarter, you can add a flag column to test for data types. Personally, I would take this data and copy it to Excel for easy sorting / viewing.

select
    a.prod_table,
    a.prod_column,
    a.prod_column_datatype,
    b.dev_table,
    b.dev_column,
    b.dev_column_datatype
from
    (select 
        t1.name prod_table,
        c1.name prod_column,
        dt1.name prod_column_datatype
     from
        prod.sys.tables t1
        join prod.sys.columns c1 on (t1.object_id = c1.object_id)
        join prod.sys.types dt1 on (c1.system_type_id = dt1.system_type_id)) a
    full outer join 
    (select 
        t2.name dev_table,
        c2.name dev_column,
        dt2.name dev_column_datatype
     from
        prod.sys.tables t2
        join prod.sys.columns c2 on (t2.object_id = c2.object_id)
        join prod.sys.types dt2 on (c2.system_type_id = dt2.system_type_id)) b
    on (a.prod_table = b.dev_table and
        a.prod_column = b.dev_column)
order by 
    a.prod_table,
    a.prod_column,
    b.dev_table,
    b.dev_column