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

thatjeffsmith SQL Developer 12 Comments

Tell Others About This Story:

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!

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Comments 12

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

    1. thatjeffsmith Post
      Author

      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.

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

    1. thatjeffsmith Post
      Author
  3. 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?

    1. thatjeffsmith Post
      Author
  4. 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

    1. thatjeffsmith Post
      Author

      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.

Leave a Reply

Your email address will not be published. Required fields are marked *