DB2 SQL query to compare 2 schemas
I have been unable to find a solution in my searching for this throughout the web, most responses recommend a tool, which leads me to believe this may be very difficult with an SQL query or something, but...
I have been told to write a query for our DB2 database, using Toad to interface, that will compare 2 schemas and provide a result if there are any differences. I know this capability exists in Toad, but for some reason our DBA does not want to use i开发者_运维百科t. Purchasing additional software is not an option.
I have very limited SQL knowledge/experience, and this is one of my 'learning' tasks. I have not made much progress and am feeling very stuck. Can anyone offer me suggestions? The help is greatly appreciated!
I assume by schemas you mean the schemas of a table, right? All that information is available through the views of the syscat
schema. Look for instance at SYSCAT.TABLES
and SYSCAT.COLUMNS
, or see the documentation for more. You can query those views like any other view/table, so to compare if two tables contain the same columns, you can do something like:
SELECT colname, colno, typeschema, typename FROM syscat.columns WHERE tabname = 'tab1' AND tabschema = 'schema'
EXCEPT
SELECT colname, colno, typeschema, typename FROM syscat.columns WHERE tabname = 'tab2' AND tabschema = 'schema'
This will, for instance, not return all the columns that are in tab1, but not in tab2.
Try IBM Data Studio, it is free, you can select any 2 objects then compare between them.
精彩评论