ThatJeffSmith

Exporting Table Column Comments to Excel using Oracle SQL Developer Data Modeler

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

Simple Search: Search for EVERYTHING with *

Simple Search: Search for EVERYTHING with *

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

Create a new report template first

Create a new report template first

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

Move over the model properties you want in the report

Move over the model properties you want in the report

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.’

The data as requested, in my spreadsheet.

The data as requested, in my spreadsheet.

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:

We want the Advanced Search

Advanced – we’re not worthy!

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!