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.
| Author |
Topic |
|
murrayb3024
Yak Posting Veteran
79 Posts |
 Posted - 2014-04-08 : 12:23:05
|
| We have a vendor who likes to make table changes and drop them on us during upgrades. The problem is they don't let us know what they are. Their product is DB2 and we pump the data into our SQL Server. I was wanting to compare tables in our SQL db to the DB2 tables ahead of the load and see if we can make the changes ahead of time and be proactive vs reactive.We have the DB2 set up as a linked server and so I want to script it to compare those tables to our SQL tables and identify the unmatched ones.Thoughts? |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-04-08 : 13:57:52
|
I am guessing that you would do this something like shown below:USE YourCurrentDatabaseGOSELECT * FROM YourDB2Server.YourDB2Database.INFORMATION_SCHEMA.TablesEXCEPTSELECT * FROM INFORMATION_SCHEMA.Tables However, I have not tried it, so there many be nuances that I am not aware of. Since INFORMATION_SCHEMA is an ANSI thing, I am guessing that it is available in DB2 as well. If not, you would need to do whatever is the equivalent of that in DB2.The example above is for INFORMATION_SCHEMA.Tables. You could do a similar thing for INFORMATION_SCHEMA.columns |
 |
|
|
murrayb3024
Yak Posting Veteran
79 Posts |
Posted - 2014-04-08 : 14:03:20
|
| I tried:USE MYDATABASENAMESELECT * FROM [DB2_OLE].[DB2].INFORMATION_SCHEMA.TablesEXCEPTSELECT * FROM INFORMATION_SCHEMA.TablesGet:The OLE DB provider "DB2OLEDB" for linked server "DB2_OLE" does not contain the table ""DB2"."INFORMATION_SCHEMA"."Tables"". The table either does not exist or the current user does not have permissions on that table.I know my account has rights. |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-04-08 : 14:22:14
|
| You may want to log on the DB2 server and see what query will retrieve the table and column information for you and use those table names in the linked query. Also, I don't know the naming convention to access the linked server in your set up. Take a look at this page and compare with your set up to deduce the syntax and naming convention for to querying the tables. http://support.microsoft.com/kb/222937 |
|
|
|
|
|
|
Script to compare tables with a twist...