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.
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

imrul
Starting Member

36 Posts

Posted - 2010-08-22 : 23:44:34
Thanks all
Go to Top of Page

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]
Go to Top of Page

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]
Go to Top of Page
   

- Advertisement -