SQL Developer Quick Tip: Reordering Columns

thatjeffsmith SQL Developer 16 Comments

Tell Others About This Story:

Do you find yourself always scrolling and scrolling and scrolling to get to the column you want to see when looking at a table or view’s data?

Don’t do that!

Instead, just right-click on the column headers, select ‘Columns’, and reorder as desired.

Access the Manage Columns dialog

Access the Manage Columns dialog

Then move up the columns you want to see first…

Put them in the order you want - it won't affect the database.

Put them in the order you want – it won’t affect the database.

Now I see the data I want to see, when I want to see it - no scrolling.

Now I see the data I want to see, when I want to see it – no scrolling.

This will only change how the data is displayed for you, and SQL Developer will remember this ordering until you ‘Delete Persisted Settings…’

What IS Remembered Via These ‘Persisted Settings?’

  • Column Widths
  • Column Sorts
  • Column Positions
  • Find/Highlights

This means if you manipulate one of these settings, SQL Developer will remember them the next time you open the tool and go to that table or view.

Don’t know what I mean by ‘Find/Highlight?’

Find and highlight values in a grid with Ctrl+F

Find and highlight values in a grid with Ctrl+F

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

Comments 16

  1. Is there a way to set Auto-Fit All Columns.Best Fit as a persistent value?
    Do I have to set it every time I execute a query?

  2. Great tips.. however, when I sort a particular column, it isn’t retained. I’m using version 4.0.0.12.
    Also, is there any way to default to the “data” tab instead of “columns” when opening a table? 99% of the time, I want to go straight to the data view. I can’t find any way to change it

  3. Is there a way to have the columns fit the data and not the screen? If I run a query that has only one or two columns, those columns are fit to the screen and it’s very ugly to look at. SqlDeveloper 3.x output data that fit the column headers, not the screen. I’m using ver 4.0.0.12.

    1. thatjeffsmith Post
      Author
  4. Hi I am a converted PLSQL Developer user and one of the greatest features that I miss from PLSQLDEV how the sorting of results are handled.

    In PLSQLDev one could sort your results quickly an easy by clicking on the column heading. Only the results currently cached will be sorted without running the query again. Unfortuantely, SQL Developer (Oracle) doesn’t work this way as it will execute the query again if you want to sort by a certain again.

    Do you think it is an enhancement that the development team can easily implement?

    1. thatjeffsmith Post
      Author

      It’s not a matter of degree of difficulty, it’s more of philosophy. Do you let the client do the sort, or do you let the server? We’re letting the server handle that.

      >>Only the results currently cached will be sorted without running the query again
      I can see this being valuable, but I also see big, potential problems. An unaware user might think that the person with the highest salary is in row 1 when actually it is a different person altogether who hasn’t actually been fetched into the grid yet.

      If you want to avoid the cost of running the query again, do the sorts in the query itself? Or maybe copy the data to Excel and do your stuff there.

  5. Very nice tip.
    A nice to have would be right click a column or table in the connections browser where you are presented with the option to load in SQL Worksheet ready for DML rather than having to drag ‘n’ drop an object onto and already opened a sql worksheet. Similarly I often use the data editor to review data would it be possible to select columns of interest, right click and be presented with “generate DML in SQL worksheet” a nice to have that would save lots of time

    1. thatjeffsmith Post
      Author

      Similarly I often use the data editor to review data would it be possible to select columns of interest, right click and be presented with “generate DML in SQL worksheet” a nice to have that would save lots of time

      I’m not sure what you mean by this. Do you mean you want to make changes in a grid, then have a method avail for SQLDev to take those changes and send the DML to the worksheet? If you COMMIT your changes, you see the DML written to the Message – Log panel. You could copy it from there.

      1. Sorry I should have been clearer. I often use the data editor to preview the contents of tables/views i.e. UCM metadata etc. Upon previewing the data within the data editor I identify which columns I want to query. It would be nice to select my columns of interest “as I am already previewing them” and have the option to right click and send the “select DML” to a sql worksheet where I can continue to query the columns of interest and apply required predicates.
        Hope this is clearer.

        One small bug (experience) in build 12-84 is that when I have >40 lines of code in the sql worksheet the window continuously scrolls back to the top when I attempt to navigate to the bottom of the sql worksheet window using either mouse of arrow keys the windows automatically scrolls back to the top.

  6. Good stuff as always, Jeff. Never even noticed the Find/Highlight before. The persistence is handy, too.

    Now all we need to do is add one more item to that context menu that says “Freeze Column”. Your tip lets us put the important columns on the left so they will be visible, but often all the data we want to see still doesn’t fit within the window width, so we still need to scroll to the right to see it.

    Freezing columns will allow us to keep the frozen columns (could be more than one) visible while the rest of the columns slide in/out of view. This way we can still see the ID/Name/etc of the row as we view the rightmost columns. Like Excel freeze panes concept.

    1. thatjeffsmith Post
      Author
      1. Done. Should have looked for that myself.

        Guess I’m a typical American. Easier to sit around and talk about the issues than it is to go vote. Thanks for the link!

    2. If you’ve got Excel, I’d recommend using it. It’s a fantastic tool for filtering, sorting, pivoting, etc. Horses for courses as we say in the UK.

  7. I have been using version v. 4.0.0.12 of the tool and was wondering if there is a way to prevent the displaying of the directional arrows on each column heading? When you have a fair amount of columns being displayed, these seem to muddy up the display.

    In addition, I was wondering if there is a way to get the grid result to not span the whole pane when being displayed? Even if I select “Auto-fit All Columns -> on Header”, the results still span the full pane. Versions prior to 4 did not seem to operate like this.

    Thanks.

    1. thatjeffsmith Post
      Author

      >>I was wondering if there is a way to get the grid result to not span the whole pane when being displayed?
      That’s a bug that’s been fixed for the next EA.

      As for the sort indicators, we’ve been tweaking those a bit. They were there in 3.2, now they’re a bit more accurate. And in the version I have they are colored to match/blend in with the column header itself. Although there’s no option to not use them at all…

Leave a Reply

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