Schema Comparison - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Schema Comparison

Author

Topic

arvind_ramugade
Starting Member

30 Posts

Posted - 2012-03-26 : 03:52:29

Hi,
Is there any way/utility to compare schema of MSSQL databases assuming both the databases reside on different SQL server ?

Regards
Arvind.

ramugade

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2012-03-26 : 05:10:53

SQLCompare or script the scehemas and use a file diff tool.

arvind_ramugade
Starting Member

30 Posts

Posted - 2012-03-26 : 05:14:41

what's the exact syntax for SQLCompare ?

ramugade

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-03-26 : 06:07:46

SQLCompare is a third-party tool offered by Redgate. Once you install it you run it and follow the prompts it displays to compare.

Another option is to create linked servers on one or both of your SQL Servers. You can then query INFORMATION_SCHEMA or system views to compare schemas. Here's an example:

-- On server A, with linked server to SERVERB:

SELECT * FROM Database1.INFORMATION_SCHEMA.COLUMNS
INTERSECT
SELECT * FROM SERVERB.Database1.INFORMATION_SCHEMA.COLUMNS  -- this will display column information that's identical on both servers

You can modify this using EXCEPT to find differences in schema, and possibly even hack it to generate a change script to synchronize them. It's probably easier to use the Redgate tool if you have a lot of changes to sync.

ChrisW1
Starting Member

1 Post

Posted - 2012-04-03 : 11:40:06

robvolk -
Have you tried running that code? I receive an error when doing so saying that there are too many parts for the table name:

SQL 2005 Error Message:
Msg 117, Level 15, State 1, Line 1
The object name '[linkedServ].[db].sys.INFORMATION_SCHEMA.COLUMNS' contains more than the maximum number of prefixes. The maximum is 3.

Does anyone know how to get around this limit?

([] around the INFORMATION_SCHEMA.COLUMNS part does not work.)

[*edit*] My mistake, you covered it -> no .sys. Thanks!

-Chris

Sigs are fun, but not this one.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources