September 18, 2014 at 4:22 am
Hi,
Need a query to compre two database schemas.
I have seen lot of comparision tools but need a query.
Thanks
September 18, 2014 at 7:46 am
Priya004 (9/18/2014)
Hi,Need a query to compre two database schemas.
I have seen lot of comparision tools but need a query.
Thanks
The reason there are tools is because you can't do this with a single query. It is immensely complicated. Consider you have to look at tables, view, procedures, functions. Then you have to look at the parameters for each one that takes parameters. You also need to look at user defined datatypes, schemas, indexes....this just isn't something you can do with a single query.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 18, 2014 at 12:29 pm
EDIT: You can pretty much ignore the stuff below. You can download SSDT for free and it has a schema comparison tool included.
There are ways to do it without buying tools, but as Sean says it is difficult. One option would be to script out both databases to different directories and then use something like windiff to compare the directories to find missing files.
Another option would be to use powershell to.
Another option is to create a script, batch or maybe powershell, that uses the tablediff utility. It isn't perfect and is really designed to compare data, but you can just compare schema and rowcounts (which you don't care about).
Long-term it is well worth your money to buy a third party product to do it. Even Visual Studio has a schema compare tool in it now.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 19, 2014 at 1:07 am
ok..if I need to compare only table schemas..can I get a query?
Also one issue is that.. two databases resides in diff servers and both them cannot be linked due to security.
September 19, 2014 at 1:33 am
Priya004 (9/19/2014)
ok..if I need to compare only table schemas..can I get a query?
For e.g. if only the collation is different on the servers (which is normal to be), you'll have differences in some objects definitions. With third-party tools you can ignore that.
Also one issue is that.. two databases resides in diff servers and both them cannot be linked due to security.
Because of that, you need a tool. You have a "Schema compare" in Visual studio (Integrated shell) which is part of Sql server data tools. Above you have a download link.
Igor Micev,My blog: www.igormicev.com
September 19, 2014 at 2:55 am
Priya004 (9/18/2014)
Hi,Need a query to compre two database schemas.
I have seen lot of comparision tools but need a query.
Thanks
Do you have replication installed on a (it can be any) SQL Server?
If you do, then installed within the SQL Binaries folder is a command-line tool called tablediff
It's not required for your DB to have replication running to use tablediff.
There are several commands/switches that you can use with 'tablediff' checkif it will work for you.
It's capable of comparing data [source] [destination] that's and generate the differences.
If Schema different you'll get an error, which help you identify it's inconsistent ..however I don't believe it will generate a script to correct that.
From another response it mentions you cannot connect the servers to each host/server directly. I agree with Igor it's best to use a specialized compare tool to do that, but if the company you are working for is on a budget using tablediff may help.
I've used RedGate and DB Artisan Compare tools. I can't remember if either was better - but both were better than writing T-SQL queries 🙂
--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply