Data models sure are handy. They tell a story – how does the information get stored as data? Just what is a ‘customer?’ Sometimes though our applications die and only leave legacy scripts behind. Or maybe you are a DBA and someone has given you a set of scripts to create some new objects in your database. You ask them for their model, and they just look at you with that weird blank stare. “We have models, you say?” Of course, geeks have been building models since at least 1985.
The Reverse Engineer, or IMPORT, feature in Oracle SQL Developer Data Modeler will read information from an existing database to build your data model. But did you know it can also read in Data Definition Language (DDL) scripts? That’s right, those directories you have laying around with hundreds of CREATE OR REPLACE… scripts can be used to build a model.
For quick and dirty modeling tasks, I say just run that directly in SQL Developer (accessible under the View menu.) If you are going to get serious and build some new models from scratch, then you should probably run the standalone Oracle SQL Developer Data Modeler program. I also find the user interface much simpler in the standalone – it’s all about modeling. Some of the more interesting modeling features are ‘hidden’ if you’re in the main SQL Developer interface.
For this brief demonstration I’ll just run this in SQL Developer.
Disclaimer: This post was written with Oracle SQL Developer v3.1.
Open your model using the View – Data Modeler – Browser interface
You can now use the IMPORT feature
Select your file(s)
If you don’t happen to have any DDL scripts laying around, feel free to build some new scripts using the new Cart feature.
Tell us what RDBMS mapping scheme to use
It’s easy to think that SQL Developer only works with Oracle, but did you know the Data Modeler works with most RDBMS systems, including DB2 z/OS, DB2 LUW, and SQL Server?
Review the results and proceed to your new model and Entity Relationship Diagram!
If there are any problems parsing the DDL script, the modeler will warn you about it. You can now review your scripts visually using the ERD. A newer feature for v3.1 allows you to automatically step into a SubView based on a Schema. So if your scripts are importing from several sources, you can look at all of it together, or just for user/database ‘x.’ Actually this feature is so new I saw it in a developer demo just today 🙂
People like to rag on java and it’s performance issues. But I was able to parse more than 1500 DDL files in less than a minute and have my model created for me. It’s still a fairly small model, only 269 tables, but that’s still pretty good if you ask me.
