Need to find schema difference between two databases

Question

Need to find schema difference between two databases

Bagath

SSCrazy

Points: 2409
More actions
September 24, 2010 at 4:21 am

#148708

Hi all,
I need the assistance for the following criteria. Could any one help/suggest me?
Need to find schema difference between two databases. Need to find changes done to objects between production and development databases.
Get the list of changes done to the database objects.
Thanks in advance

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

For jobs like this I recommend getting a copy of RedGate's SQLCompare. It'll likely cost less than the time required to write such a solution from scratch.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Bagath SSCrazy Points: 2409 More actions · Answer 2

Thanks you for the suggestion. However any other alternate way like script.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Sure, but it's terribly complex. Use the system views (sys.objects, sys.columns, sys.indexes.....) to build up queries to get the definitions and then compare the resultsets between the servers. It'll be a fairly time-consuming script to write.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Bagath SSCrazy Points: 2409 More actions · Answer 4

Thanks again. I echo with your words, developing queries for the above criteria is complex and time consuming at the same time there are no permission to use the tools from my end.

Please give me the sample script if any one worked/have on the criteria like above.

Thanks,

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Bagath (9/24/2010)
Please give me the sample script if any one worked/have on the criteria like above.

I use SQLCompare when I'm doing work like that. Search the script library here, there maybe something that'll give you a start

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 6

You might be able to find a free software that could help you here: http://www.supershareware.com/sql-server-comparison-free/

I'd go for something like that rather than try to write it myself.

Do you have Visual Studio Team System? If so, there's a schema comparison tool there. It's not as good as Red Gate's, but it'll work.

You might try this one;

http://xsqlsoftware.com/

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Bagath SSCrazy Points: 2409 More actions · Answer 7

Thanks you.

I will try to find/write out the tool/script.

Once again thanks to all for the valuable suggestions and help.