Compare Database Schema - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Compare Database Schema

Author

Topic

imrul
Starting Member

36 Posts

Posted - 2010-08-19 : 03:54:46

Hi All, Can any one tell me how can I compare the schema of two databases. I have same database with different version, now need to know what changes are made in updated system.

Llewellyn
Starting Member

13 Posts

Posted - 2010-08-19 : 09:05:06

I am not aware of anything in SQL that does it but you can use 3rd part products like SQL Compare from Red-Gate. http://www.red-gate.com/
The other way in which I have done this in the past is to use source safe. Script the first database and add it to source safe. Check out the script from source safe and the write over the file on your machine with the script from the second database. Check in the second script. Right click on the file in source safe and select "Show history". Select the two entries and click on "Diff" this will show you the difference between the two scripts.

parody
Posting Yak Master

111 Posts

Posted - 2010-08-19 : 11:51:34

Visual Studio Team Suite has a schema compare tool if you have it.

Depending on the level of comparison you need it would be quite easy to write a comparision using system tables sys.objects, sys.columns etc

parody
Posting Yak Master

111 Posts

Posted - 2010-08-19 : 11:54:25

also there is a free MS tool called tablediff.exe its command line and a bit ugly but does the job

http://msdn.microsoft.com/en-us/library/ms162843.aspx

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-08-19 : 12:19:04

master|Views|SystemViews


SELECT [TABLE_CATALOG]
      ,[TABLE_SCHEMA]
      ,[TABLE_NAME]
      ,[COLUMN_NAME]
      ,[ORDINAL_POSITION]
      ,[COLUMN_DEFAULT]
      ,[IS_NULLABLE]
      ,[DATA_TYPE]
      ,[CHARACTER_MAXIMUM_LENGTH]
      ,[CHARACTER_OCTET_LENGTH]
      ,[NUMERIC_PRECISION]
      ,[NUMERIC_PRECISION_RADIX]
      ,[NUMERIC_SCALE]
      ,[DATETIME_PRECISION]
      ,[CHARACTER_SET_CATALOG]
      ,[CHARACTER_SET_SCHEMA]
      ,[CHARACTER_SET_NAME]
      ,[COLLATION_CATALOG]
      ,[COLLATION_SCHEMA]
      ,[COLLATION_NAME]
      ,[DOMAIN_CATALOG]
      ,[DOMAIN_SCHEMA]
      ,[DOMAIN_NAME]
  FROM [master].[INFORMATION_SCHEMA].[COLUMNS]

might be of help as well

If you don't have the passion to help people, you have no passion

imrul
Starting Member

36 Posts

Posted - 2010-08-22 : 23:44:34

Thanks all

sualeh
Starting Member

1 Post

Posted - 2010-08-24 : 23:22:59

Definitely try out SchemaCrawler for SQL Server. It is a free tool designed to be able to compare schemas. [url]http://schemacrawler.sourceforge.net/[/url]

Sergii
Starting Member

4 Posts

Posted - 2010-12-20 : 05:34:07

Devart has really good schema comparison tool. I don't really use it often but sometimes it helps me and saves my time.

[url]http://www.devart.com/dbforge/sql/schemacompare/[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources