SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Script to compare tables with a twist...
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

murrayb3024
Yak Posting Veteran

79 Posts

Posted - 04/08/2014 :  12:23:05  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3559 Posts

Posted - 04/08/2014 :  13:57:52  Show Profile  Reply with Quote
I am guessing that you would do this something like shown below:
USE YourCurrentDatabase
GO

SELECT * FROM YourDB2Server.YourDB2Database.INFORMATION_SCHEMA.Tables
EXCEPT
SELECT * 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
Go to Top of Page

murrayb3024
Yak Posting Veteran

79 Posts

Posted - 04/08/2014 :  14:03:20  Show Profile  Reply with Quote
I tried:

USE MYDATABASENAME

SELECT * FROM [DB2_OLE].[DB2].INFORMATION_SCHEMA.Tables
EXCEPT
SELECT * FROM INFORMATION_SCHEMA.Tables

Get:

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

James K
Flowing Fount of Yak Knowledge

3559 Posts

Posted - 04/08/2014 :  14:22:14  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000