January 5, 2009 at 2:06 pm
Hi everyone,
I have a question concerning the a fast way to compare the fields between two databases. Let's say two databases are pretty similar--they have the same tables, for the most part. However, there are small differences between the fields of the tables. Some fields are named slightly differently, some fields may be in one database that aren't in the other, and some fields with the same name may have two different types in the different databases. As a result, some software isn't interfacing properly with one of the databases.
I'm looking for a fast, somewhat automated way to identify the differences between the two databases. It'd be nice to have a list of fields that are in database A, but not database B, vice versa, and a list of fields whose types differ between the two databases. Does anyone know of a utility that does such a thing?
If there isn't one available, I was thinking of coming up with some way of using Excel to compare the names of the fields. In this case, my question would be: what is the best way to get the list of fields (and their types) of all the tables in a database in an easily parsible text file? I was thinking I could mainly use the Script to --> Insert option, but I was hoping that there was a better way.
Any advice anyone could give concerning these issues would be greatly appreciated.
January 5, 2009 at 2:16 pm
I use Redgates SQL Compare to perform these kinds of comparisons. There are other tools available that do the same thing, some are even free.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 5, 2009 at 2:37 pm
I also use RedGate SQLCompare, but if the databases are on the same server you can write a query using the information_schemas views and get a rough comparison of column names, types, etc...
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
January 5, 2009 at 5:10 pm
Try this:
Select * From TargetDB.INFORMATION_SCHEMA.COLUMNS
EXCEPT
Select * From ReferenceDB.INFORMATION_SCHEMA.COLUMNS
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 5, 2009 at 9:07 pm
Here is another quick and dirty way to compare if both databases are on the same server. You probably want to take a look at information_schema.columns and add a few more things like ordinal position, is nullable, etc.
SELECT
DB1.TABLE_CATALOG AS DB1
,DB2.TABLE_CATALOG AS DB2
-- table and column names
,COALESCE(DB1.TABLE_NAME, DB2.TABLE_NAME) AS TABLE_NAME
,COALESCE(DB2.COLUMN_NAME, DB1.COLUMN_NAME) AS COLUMN_NAME
-- any other column attributes you want to list/compare
,DB1.DATA_TYPE AS DB1_DATA_TYPE
,DB2.DATA_TYPE AS DB2_DATA_TYPE
,CASE WHEN DB1.DATA_TYPE = DB2.DATA_TYPE THEN 1 ELSE 0 END AS Match_Fl
FROM
(
SELECT
TABLE_CATALOG
,TABLE_SCHEMA
,TABLE_NAME
,COLUMN_NAME
,DATA_TYPE
FROM
DB1.information_schema.columns -- change to name of actual db
) DB1
FULL OUTER JOIN
(
SELECT
TABLE_CATALOG
,TABLE_SCHEMA
,TABLE_NAME
,COLUMN_NAME
,DATA_TYPE
FROM
DB2.information_schema.columns -- change to name of actual db
) DB2
ON DB1.TABLE_NAME = DB2.TABLE_NAME
AND DB1.COLUMN_NAME = DB2.COLUMN_NAME
ORDER BY
3,4
January 5, 2009 at 9:21 pm
The attached script gives a little more column level detail. If you are comparing a prod database to a dev or QA database you could run the query in each one then copy the results to separate excel worksheets.
Then on the prod and a QA excel worksheets concatenate all of the columns together and use the countif() excel function to see if the concatenated values exist on both worksheets. If it’s not an exact match countif() will return a zero and you can probably eyeball the differences from there.
Hope that helps.
January 6, 2009 at 11:33 am
I'm going to add a vote for "buy something that does this for you". RedGate's Compare is good. I use ApexSQL Diff, because it compares both structure and data in a single product. There might be others.
Get a free trial, then buy the one you like best. Don't even try to reinvent the wheel on this one.
They will also come in very, very handy when you want to synchronize Dev with Test or Test with QA or QA with Prod, and so on. They pay for themselves very rapidly because of things like that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 6, 2010 at 1:58 pm
Short, Sweet and to the point !! THANKS !! 😀
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply