Wednesday, February 02, 2011

Data Dictionary Posters and Automatic FK discovery

We have all had posters on the wall at some point that are the data dictionary.  That is if you made it to the booths before they ran out at Oracle Open World or other events.  There's a few new features in the FREE Oracle SQL Developer Data Modeler that went production this week that can help avoid the need for the posters.  


First we have to import from the data dictionary, the data dictionary.


I'm filtering to just the DBA_T* views.  This filter is feature #1 that helps in prior releases it was individually selecting the objects to import, select all, and select none.  This helps to narrow down and then do a select all to the views you want to see.


Now that we have the views imported, the next step is to convert the views to tables, Feature #2.  Since views don't have FKs we swap them over to tables.  The end result of this wizard will create tables all prefixed with T_  .  This is done because the views remain in the model.  There's ways around this like bulk renaming the views from DBA_ to V_DBA_ then the tables could take the base name but that's for another day/blog.



Now that we have tables, we just have to put a primary key on the main driving table.  In this case, tablespace_name.





At this point we have a model which has a few tables and only one of which has a primary key.  This is the same place we could be if we import some tables from the first step in the import wizard.


Now for the main event , feature #3,  right click on the relational model in the menu and choose "Discover Foreign Keys"


You will get a list of the foreign keys that have been found. Click Ok.


The end result is a model of the data dictionary on the subset of views/tables with the proper lines connecting.