Menu
I have to do checks between a development and release database and do this manually, which is both slow and not 100% reliable (I only visually inspect the tables).
Is there a quick and easy way to compare table schemas automatically? Maybe even a feature that does this built right into SQL server?
Edit: I'm comparing structure only, thank you for pointing this out.
282
Without the use of third party tools, one way to compare table schemas using native T-SQL is to query the INFORMATION_SCHEMA. COLUMNS metadata and build a custom made procedure or logic that compares two tables. Another way to do this is to use the dynamic management function sys.
Select Id_pk, col1, col2...,coln from table1 MINUS Select Id_pk, col1, col2...,coln from table2; You can quickly check how many records are having mismatch between two tables. The only drawback with using UNION and MINUS is that the tables must have the same number of columns and the data types must match.
I'm a fan of SQL DBDiff, which is an open source tool you can use to compare tables, views, functions, users, etc. of two instances of SQL Server databases and generate a change script between the source and destination databases.
113
There are some commercial products that do this; Visual Studio 2010 Premium Edition can compare schemas.
Some others:
http://www.red-gate.com/products/SQL_Compare/index.htm
http://www.apexsql.com/sql_tools_diff.aspx
36
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
