I love it when I get product and technology questions from our users. More often than not, it leads to me citing a blog post on the subject, or even better, inspires new writing material. And that’s the case for today’s post:
Is there an easy way to fake the search tool into getting all the tables in a design so I can export the column comments for everything? The CSV export does not appear to do comments.
The tool in this case is Oracle SQL Developer Data Modeler. I’m going to be using the latest Early Adopter release, v3.3. You can download and use this as well, although it’s in a ‘beta’ state – so be aware it hasn’t been cleared for production use and support yet.
One of the new features for 3.3 is a new and improved search.
Search results can be exported to Excel – so you can see where I’m going with this post. What we need to be able to do is search the entire model for everything and then create a custom report template to only include the data we want.
Step One: Do the Search
A couple of notes:
- Search using a wildcard (*) – this will find EVERYTHING and ANYTHING
- Set the scope of the model search to Columns – once filtered, the ‘Report’ results button will be available
Create the Report
Note that the search returned all of the column information in our model – way more data than I want to include in my report. So I’ll want to create a custom report template to just include the desired model properties. This is even easier in the Advanced Search as it allows you to search on specific object properties e.g. ‘Comments in RDBMS’ versus everything.
Custom Report Template
Note you can change the column names for the generated reports – the column labels in the report, not the actual table column names 🙂
Exporting the Results
Once you’ve saved your template, click OK to get back to the main report dialog. Select your new template, report type – I’m picking XLS – then click ‘Generate Report.’
You’ll remember the original question was how to get this data in CSV. Well, once you have the data in Excel, it’s a simple Save As operation in your spreadsheet to get it into the comma-separated-values format.
I LOVE the new Search Feature
I hope this post starts to show why. The flexibility of the search, and of the search results reporting, means you can take your model data and pretty much massage it to do whatever you want. And don’t forget the search support regular expressions, so feel free to go crazy!
We have a small bug in the current EA that prevents the Advanced Search results to report properly. This has already been fixed for the production release. Once it goes, you could have done the same exercise like so:
The advanced search lets me set a search term for one or more model object properties. The reporting process is the same for the Advanced Search.
Happy reporting!
14 Comments
Can we schedule this /automate to run daily?
Data Modeler has no scheduling or CLI..but if you use the reporting repository, you could schedule SQLcl to run a select script export to CSV quite easily.
Jeff, very much like the reporting functionality. I see in your post under “Exporting the Results” that the comments are spanning more than one line. This is not what I see happening at my end when an Excel report gets generated. I have to do couple extra steps – unprotect the worksheet and then do an autofit. Would be great if this would happen automatically, i.e. autofit. For example, to compare, the pdf version looks fine.
can we schedule the export
No…but if you’re using the optional reporting repository, you could write a query to grab your column comments, and then use the Cart to export that to a XLS, and then use the CLI in sqldev…so yes 🙂
But the Data Modeler has no command line interface or scheduling engine.
Hi Jeff,
I have a requirement.
Based on the above explanation we can rename the existing attributes of a entity. But “Can we add the attributes for the existing entities” Using any excel, ODBC or other Options??
Thanks in Advance,
Niharika
Thanks a loads Jeff. That was of great help 🙂 Kudos 🙂
Anytime! Thanks for your support as well.
Hi Jeff,
That was a great option. Can you please tell me is there any import option available. Once we have modified the exported the xls?
Yes! Just right click on your design in the tree, and say ‘update from previously exported to xls’ … or similar to that 🙂
Just used the feature to export table comments, edit, upload bag to the model. Worked great!
Yes. Ah great thanks as it’s always difficult to discover it in the documentation in 5 minutes.
I ask me also what kind of library is used to:
– create the XML files of the data model (business info, datatype, logical, mapping,….). Is it Xstream ?
– generate the report, I suppose that you use XSLT ?
And why SQL Developer, don’t use an (Oracle) Database as persistence store ?
Is it not easier if you want to extract and generate reports ?
Just because I’m curious.
Thanks
Nico
Not everyone has access to a database. And many people have access to a database but lack the privileges to create a repository. Keeping things file based makes it very easy to deploy and move around without worrying about database backup and recovery procedures.
All that being said, we frequently get requests to adding a database based repository and it’s on our list of things to consider going forward.
Thanks Jeff! Works beautifully (if you don’t try the advanced search that is).